Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all ,
I have got the similar data and multiple iterations of this across several dates. What i would like to capture is the movement for Claims Amount between 2 different dates (allow the user to select the variability of comparison dates like 4 weeks , 12 weeks etc ) and categorise them under
positive movement
negative movement
no Movement
as an example i have the following data
date |
Business Unit |
Claim ID |
Claim Amount |
Week 4 |
AAA |
a1 |
10 |
Week 5 |
AAA |
a1 |
20 |
Week 6 |
ABC |
a2 |
20 |
Week 7 |
ABC |
a2 |
10 |
Week 8 |
BCA |
a3 |
10 |
Week 9 |
BCA |
a3 |
10 |
I would like to show is when the user select Claims Id or Business Unit then show the movement
Date |
Claim id |
current Amount |
Last week Amount |
Movement |
Week 5 |
a1 |
20 |
10 |
10 |
when there’s no movement
Date |
Claim id |
current Amount |
Last week Amount |
Movement |
Week 9 |
a3 |
10 |
10 |
0 |
In your script, load the data sorted by id/week/.. and use the peek() function to get the amount from the previous row in order to calculate the movement between rows. Something like:
Load Week, ID, Amount,
Amount - if( ID = Peek('ID'), Peek('Amount'), Amount) As Movement // if I find the same ID on the previous loaded row, then I get the amount, else I use the current amount to get the Movement = 0
Resident Table1
SORT BY Week, ID;
You can finetune your logic with the weeks number checking, if you want.
Then, in your report, you sum up the movements.
@luciancotea Thank you so much I will be testing the solution above now and give feedback
@luciancotea I tried the above solution but is not working, is there another alternative solution I can try?
I tested the above with the inline load and it is working fine but when I take the same logic and apply it to the data that's coming from the SQL tables to the model it is seems it is not working fine.