topic Re: Find previous value in load script in QlikView

Find previous value in load script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Date	Name	SuscEnabled	Income
1/1/2018	Person1	Yes	$5
1/1/2018	Person 2	Yes	$2
2/1/2018	Person 1	Yes	$8
2/1/2018	Person 2	Yes	$5
3/1/2018	Person 1	No	$0
3/1/2018	Person 2	Yes	$5
4/1/2018	Person 1	No	$0
4/1/2018	Person 2	Yes	$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

Date	Earn 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

Re: Find previous value in load script

Qrishna — Thu, 08 Mar 2018 01:24:24 GMT

Hope this helps to some extent.See attached.

result:

Re: Find previous value in load script

jonathandienst — Thu, 08 Mar 2018 05:01:37 GMT

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;

Re: Find previous value in load script

Anonymous — Thu, 08 Mar 2018 14:39:10 GMT

Thanks Krishna for four feedback!

Re: Find previous value in load script

Anonymous — Thu, 08 Mar 2018 14:39:48 GMT

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