Debtors Overdue History:- Set Analysis & AsOfWeek Issue

[robert99]

Hi

Hope someone can help on this

The issue I'm having is comparing 30 60 90 etc DEBTOR days overdue over time. With just the unadjusted data I get a  nonsense figure due to the credits not allocated. I want to exclude the credit value by customer. For example 86417 credit compared too 130429 debit with the credits excluded

The two measures were as follows

AR Over 120 Days O/S

sum({<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate}

>} if( ARDueDate+121 <AsOfWeek ,

ARValue))

Over 120 Alt   (alternative measure where negative values were excluded)

sum({$<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate}

,CustAccNum = {"=sum({$<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate} >}if( ARDueDate+121 <AsOfWeek , ARValue))>0"}

>}if( ARDueDate+121 <AsOfWeek , ARValue))

Both these figures gave the correct value AS LONG AS ONE ASOFWEEK was selected

As did other alt measures too

The problem I have is the alt measure doesn't work with AsOfWeek as a dimension

(unless I filter by AsofWeek 1/6/2018)

The unadjusted figure is correct but the alternative isn't

I think the issue is AsOfWeek doesn't work in set analysis as I've done it but I haven't been able to fix it yet (Ive tried lots of different options but non worked

And help will be appreciation

Thanks

[robert99 · Accepted solution]

https://community.qlik.com/blogs/qlikviewdesignblog/2015/02/16/set-analysis#start=25

jonascbi Aug 13, 2018 6:41 PM (in response to Robert Hutchings)

HI Robert,

I don't fully understand your use case - I'll make an educated guess that you're trying to exclude certain accounts from contributing towards your totals if the net position per month is = zero. There must be a bit more to it though as if the net position per month per account is zero - it won't impact the totals, so why take it out? Anyway, disregarding my requirement knowledge gap - to support this type of logic you'll need to make use of the Aggr statement. It allows you to compute figures at a grain of your choosing, so you can do your sum(Value)>0 check per account per month and sum up the remainder. Something like the following should do the trick:

sum(aggr(if(Sum(ARValue)>0,sum(ARValue)),CustAccNum,AsOfMonth))

... or trying to second-guess your actual business logic requirement, minor tweak to ignore any selections in your net zero check:

sum(aggr(if(Sum({1} ARValue)>0,sum(ARValue)),CustAccNum,AsOfMonth))

Hope it helps & happy Qliking!

Best

Jonas