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
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
@dadumas try below
sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>}[Current Trx Amount]))>$(vGEAmount)"}>} [Current Trx Amount])
I like this simplified logic, but I get the "Error in set modifier ad hoc element list [,] or[ )] expected". I am guessing that it does not like 2 strings even if one uses double quotes and 1 used single quotes.
@dadumas I don't see any errors and there is not issue with quotes as well. Can you share screenshot of your expression to look at?
It is literally this:
sum({$<[Customer Number]={"=fabs(Sum(<[Days Aged]={'>60'}> [Current Trx Amount]))>$(vGEAmount)">} [Current Trx Amount])
I do not see red in the expression editor, but once I clik apply, or ok, the table chart gives me that error
Sorry, this gives the error:
sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>$(vGEAmount)">} [Current Trx Amount])
@dadumas there is syntax issue in your expression
sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>$(vGEAmount)"}>} [Current Trx Amount])
This is the one I am using without that extra }, same error...
sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>$(vGEAmount)">} [Current Trx Amount])
@dadumas the error which I highlighted actually you need to include it and not to remove it. So add that bracket as highlighted in my previous reply
I changed back to this:
sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>25000"}>} [Current Trx Amount])
I get no error, but the embedded logic of {<[Days Aged]={'>60'}>} is ignored. I still see invoices that are less than 60 days old.
So, if we can get that piece to work, its done.