So I have two tables, one contains payment history another contains account information, they are related by accounts.
I created this if statement: IF( calculate (sum($), transactions date <= dateadd(accountenterdate,3,months))=0, 0, else calculate (sum($), transactions date <= dateadd(accountenterdate,3,months)))
This is the problem, I want the average per account so im using this if statement in an AverageX statement, so that for each row it will calculate how much was paid within 3 months of being booked. For instance I have 8 accounts that were entered on the account table, 7 of the accounts paid and have a transacion history, 1 account did not. So when I do the averagex statement it takes the average of the 7 accounts that paid and are on the transaction table. How can I change it so that even if the account does not have payment history it is still included in the average.
right now if i filter to a group of accounts that totals to 8, 7 of which paid, because of the if statement 0 will be provided for all accounts outside the group that made a payment but not the 1 account that has no transaction history. Is there a DOES NOT EXIST option?
