# App Development

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for
Did you mean:
Creator II

## Days Sales Outstanding by Sales exhaustion (question rephrased)

Hi community,

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?

Lisa

Labels (2)

• ### RangeSum()

10 Replies
Creator II
Author

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

Creator II
Author

Any ideas for this recursive challenge?

Creator II
Author

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

Creator II
Author

Soo, nothing?

I'm still struggling with this 😞

Employee

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?

Creator II
Author

Hi Dalton,

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

Thanks

Employee

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.

Creator II
Author

hI @Dalton_Ruer ,

My code looks like this now:

``````DSOMethod:
%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?