Days Sales Outstanding by Sales exhaustion (question rephrased)

[valpassos]

{Please delete post as it was redundant in the community}

[valpassos]

Hi!

Do you think RangeSum(Above(...))is the answer for this challenge? I'm not being able to use it, but maybe I'm using the functions incorrectly.

Thanks

[valpassos]

Any ideas for this recursive challenge?

[valpassos]

Community, do you think you can point me in some direction?

[valpassos]

Soo, nothing?

I'm still struggling with this 😞

[Dalton_Ruer]

Can you perform the calculations in the load script and take advantage of PEEK/PREVIOUS and GROUP BY? 

Or are there things that you would need to adjust based on active selections? 

[valpassos]

Hi Dalton,

How would that work? This needs to be  a recursive calculation somehow. Can you show me an example?

Thanks

[Dalton_Ruer · Accepted solution]

It's not recursive, just sequential. Here is an example post with lots of interaction and examples. 

https://community.qlik.com/t5/QlikView-App-Dev/How-to-use-Peek-function/td-p/701647

Basically there are 2 functions that allow you to look at the previous field value while you are on the current row of data. So that's why you sort things correctly. The PREVIOUS command will look at the previous rows value IN THE DATA. But if the data doesn't actually have a value then you need to use the PEEK command to get the previous assigned value for a field. 

Logically you would be saying "If this is the same KEY CUSTOMER I had on the PREVIOUS row then I want to subtract the PREVIOUS (field to use value) from THIS_ROW_FIELD_VALUE to produce a difference. Then I will do whatever with that value to create a new field. If you just need to read previous rows values that exist then PREVIOUS is what you need. If you need to read one of the calculations you did on the fly for the previous row, then you need to use the PEEK function. If you look them up in help they both have examples in addition to the community post I shared above. 

[valpassos]

hI @Dalton_Ruer ,

My code looks like this now:

DSOMethod:
LOAD 
%KEY_Customer
    ,YearMonth
    ,SUM([#Due_Amount])						AS #Due_Amount
    ,SUM([#Sales_Amount])					AS #Sales_Amount
    ,if(%KEY_Customer=PREVIOUS(%KEY_Customer),PREVIOUS(SUM([#Sales_Amount]))) as #Previous_Sales_Amount
RESIDENT FactTable
WHERE [_FactType]='DUE' OR [_FactType]='SALES'
GROUP BY [%KEY_Customer],YearMonth
ORDER BY YearMonth DESC;

I'm grouping by YearMonth because I need to have my due and sales figures at a Monthly level and not on a daily level.

The Previous() logic to get the #Previous_Sales_Amount is not working. I've tried with PEEK() and it also doesn't work. I've tried to compute this new field in a resident load (resident DSOMethod) but it still gives me 0 values. If I can't even retrieve the previous value, how can I even perform the difference I want to make, sales month by sales month until I reach a negative difference with the #Due_Amount?

Thanks for your help!

Lisa

[Dalton_Ruer]

Your Order by would need to be the same as Group By and you may not need group by if you don't need to aggregate. 

You need an ELSE condition if your IF statement so that you can create the previous sales amount for the first time each new KEY is encountered.