Discussion board where members can learn more about Qlik Sense App Development and Usage.
I'm trying to calculate Days Sales Outstanding by Exhaustion of Sales. It's called this way because we need to progressively subtract the Sales amount from what is in Debt, month by month, until the difference is negative (until the sales get exhausted, that is).
This is what I have and what I want:
The 576,780€ is my Due amount, in 2020-08.
In 2020-08, to the 539,955€ amount, I need to subtract the Sales amount of 2020-07. Then, to that result, I need to subtract the Sales Amount of 2020-06. This goes on progressively, until the difference is negative. Each complete month of sales exhausted represents 30 days.
The value in 2020-07 is obtained by 539,955€ - 34,171€ = 505,784.
The value in 2020-06 is obtained by 505,784€ - 22,929€ = 482,856€.
In terms of days, in 2020 August I will have (539,995*30)/539,995 = 30. And so on.
BUT when I reach the negative difference, which will happen in 2019-02, I need to divide my last cumulative subtraction by my Sales amount of that month, so my number of days sales outstanding in this case will be a percentage of 30 days ( (DSO Receivables left (Month X-1)/Sales Month X) * 30) ).
The "What I want (auxiliar values)" column is not really what I want, but maybe is necessary. What I really want is the Days (Sales Outstanding). And I will need to use this metric in line charts, tables, etc. By Customer.
Any idea on how can I achieve the last 2 columns?
Thanks in advance,
It's not recursive, just sequential. Here is an example post with lots of interaction and examples.
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.
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!
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.