Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wish to show accounts receivable invoice data based on the [Current Trx Amount] measure, but only for customers that (1) have an absolute value of their AR sum total total greater than a desired amount and (2) additionally only consider AR invoices that are >60 days old..
I use the variable $(vGEAmount) for the aggregate amount, set by the user in an input box. Example: $25,000, or ($25000). [Days Aged] is the number of days old for an invoice, and is stored as an attribute in the invoice record.
Currently this works fine:
sum({$<[Customer Number]={"=fabs(Sum([Current Trx Amount]))>$(vGEAmount)"}>} [Current Trx Amount])
However, the above does not yet additionally filter for AR invoices that are >60 days old.
So, I added this, and it does not error out, but ignores the first part of the filter above(ignores aggregate total search condition):
sum({$<[Customer Number]={"=fabs(Sum([Current Trx Amount]))>$(vGEAmount)"}, [Days Aged]={">60"}>} [Current Trx Amount])
I have also tried this:
sum({$<[Customer Number]={"=fabs(Sum([Current Trx Amount]))>$(vGEAmount)"}> + <[Days Aged]={">60"} [Current Trx Amount])>}
I have also tried this:
sum({$<[Customer Number]={"=fabs(Sum([Current Trx Amount]))>$(vGEAmount)"}> * <[Days Aged]={">60"} [Current Trx Amount])>}
None work except for the very first one at the top.
Dave
@dadumas add this to outer set as well
sum({$<[Days Aged]={">60"},[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>25000"}>} [Current Trx Amount])
Home Run - out of the park! Works perfect.
My final is:
sum({$<[Days Aged]={">$(vGEDays)"},[Customer Number]={"=fabs(Sum({<[Days Aged]={'>$(vGEDays)'}>} [Current Trx Amount]))>$(vGEAmount)"}>} [Current Trx Amount])
Once I got it working with your outer suggestion, I created another input variable. You are a hero!
Dave