Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Find previous value in load script

Hi everyone! Nice to be in touch with you again. I'm stuck in this scenario, and I'm needing some help.

Let's say I'm having a table like this. It shows income per day from people suscribed to a service

DateNameSuscEnabledIncome
1/1/2018Person1Yes$5
1/1/2018Person 2Yes$2
2/1/2018Person 1Yes$8
2/1/2018Person 2Yes$5
3/1/2018Person 1No$0
3/1/2018Person 2Yes$5
4/1/2018Person 1No$0
4/1/2018Person 2Yes$1

Now heres the situation I'm stucked. I need to build a table with the previous income where the suscription enabled change from "yes" to "no", so I can see the income loss from one day compared with the previous day. Something like this

DateEarn lost due cancellation
1/1/2018$0
2/1/2018$0
3/1/2018$8
4/1/2018$0

Here the table shows that in the day 3/1/2018 I stop earning $8 compared to day 2/1/2018 because person 1 cancelled the suscription.

But the table shows $0 on 4/1/2018 because the status of cancellation of Person 1 occurs 2 days ago, and I'm only watching for changes 1 day ago.

The question I'm asking to count or not is:  SuscEnabled = "No" and previous SuscEnabled = Yes, then sum previous income

Do you know how can I do this?

Thanks in advance

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Needs to be in a sortable data source (database or resident load):

LOAD Date,

  Name,

  Income,

  If(SuscEnabled = 'No' And Previous(SuscEnabled) = 'Yes', Previous(Income), 0) as LostIncome,

Resident Data

Order by Name, Date;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
krishna_2644
Specialist III
Specialist III

Hope this helps to some extent.See attached.

result:

1.PNG

jonathandienst
Partner - Champion III
Partner - Champion III

Needs to be in a sortable data source (database or resident load):

LOAD Date,

  Name,

  Income,

  If(SuscEnabled = 'No' And Previous(SuscEnabled) = 'Yes', Previous(Income), 0) as LostIncome,

Resident Data

Order by Name, Date;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Thanks Krishna for four feedback!

Anonymous
Not applicable
Author

Awesome. This is what i was looking for. Thanks!!