Calculate Data Movement between weeks in QV or QS

[lindelanifakude]

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

[luciancotea]

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.

[lindelanifakude]

@luciancotea  Thank you so much I will be testing the solution above now and give feedback

[lindelanifakude]

@luciancotea  I tried the above solution but is not working, is there another alternative solution I can try?

[lindelanifakude]

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.