Accounts receivable aging set analysis with multiple conditions

[dadumas]

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

[Kushal_Chawda]

@dadumas  try below

sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>}[Current Trx Amount]))>$(vGEAmount)"}>} [Current Trx Amount])

[dadumas]

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.

[Kushal_Chawda]

@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?

[dadumas]

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

[dadumas]

Sorry, this gives the error:

sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>$(vGEAmount)">} [Current Trx Amount])

[Kushal_Chawda]

@dadumas  there is syntax issue in your expression

sum({$<[Customer Number]={"=fabs(Sum({<[Days Aged]={'>60'}>} [Current Trx Amount]))>$(vGEAmount)"}>} [Current Trx Amount])

[dadumas]

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])

[Kushal_Chawda]

@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

[dadumas]

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.