Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hope this helps to some extent.See attached.
result:
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;
Thanks Krishna for four feedback!
Awesome. This is what i was looking for. Thanks!!