Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
valpassos
Creator II
Creator II

Days Sales Outstanding by Sales exhaustion (question rephrased)

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

1 Solution

Accepted Solutions
Dalton_Ruer
Employee
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. 

View solution in original post

10 Replies
valpassos
Creator II
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

valpassos
Creator II
Creator II
Author

Any ideas for this recursive challenge?

 

valpassos
Creator II
Creator II
Author

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

valpassos
Creator II
Creator II
Author

Soo, nothing?

I'm still struggling with this 😞

Dalton_Ruer
Employee
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? 

valpassos
Creator II
Creator II
Author

Hi Dalton,

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

Thanks

Dalton_Ruer
Employee
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. 

View solution in original post

valpassos
Creator II
Creator II
Author

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
Employee
Employee

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.