Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
dadumas
Creator II
Creator II

Accounts receivable aging set analysis with multiple conditions

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

 

 

Labels (1)
1 Solution

Accepted Solutions
dadumas
Creator II
Creator II
Author

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

View solution in original post

11 Replies
Kushal_Chawda

@dadumas  try below

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

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.