topic Re: Accounts receivable aging set analysis with multiple conditions in QlikView

Accounts receivable aging set analysis with multiple conditions

dadumas — Fri, 15 Nov 2024 23:56:38 GMT

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

Re: Accounts receivable aging set analysis with multiple conditions

Kushal_Chawda — Mon, 21 Sep 2020 21:16:20 GMT

@dadumas try below

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

Re: Accounts receivable aging set analysis with multiple conditions

dadumas — Mon, 21 Sep 2020 21:38:34 GMT

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.

Re: Accounts receivable aging set analysis with multiple conditions

Kushal_Chawda — Mon, 21 Sep 2020 22:26:21 GMT

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

Re: Accounts receivable aging set analysis with multiple conditions

dadumas — Mon, 21 Sep 2020 22:50:25 GMT

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

Re: Accounts receivable aging set analysis with multiple conditions

dadumas — Mon, 21 Sep 2020 22:53:07 GMT

Sorry, this gives the error:

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

Re: Accounts receivable aging set analysis with multiple conditions

Kushal_Chawda — Mon, 21 Sep 2020 22:55:23 GMT

@dadumas there is syntax issue in your expression

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

Re: Accounts receivable aging set analysis with multiple conditions

dadumas — Mon, 21 Sep 2020 23:17:09 GMT

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

Re: Accounts receivable aging set analysis with multiple conditions

Kushal_Chawda — Mon, 21 Sep 2020 23:26:59 GMT

@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

Re: Accounts receivable aging set analysis with multiple conditions

dadumas — Mon, 21 Sep 2020 23:31:02 GMT

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.

Re: Accounts receivable aging set analysis with multiple conditions

Kushal_Chawda — Mon, 21 Sep 2020 23:38:07 GMT

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

Re: Accounts receivable aging set analysis with multiple conditions

dadumas — Mon, 21 Sep 2020 23:57:12 GMT

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