topic Calculate Data Movement between weeks in QV or QS in App Development

Calculate Data Movement between weeks in QV or QS

lindelanifakude — Wed, 06 Sep 2023 20:15:20 GMT

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

Re: Calculate Data Movement between weeks in QV or QS

luciancotea — Wed, 06 Sep 2023 22:12:58 GMT

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.

Re: Calculate Data Movement between weeks in QV or QS

lindelanifakude — Thu, 07 Sep 2023 07:10:47 GMT

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

Re: Calculate Data Movement between weeks in QV or QS

lindelanifakude — Fri, 20 Oct 2023 08:50:12 GMT

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

Re: Calculate Data Movement between weeks in QV or QS

lindelanifakude — Fri, 20 Oct 2023 10:59:03 GMT

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.