Reduce Sales with previous calculated value

[luke1986]

Hey Folks,

Iam stuck with a problem.

The Blue Color is my sample Data and the Orange is the output that should be generated in Script. So basically i need to multiply the Sales with the % Value. The Output is "New Sales X Value", after that, i need to reduce the "New Sales" with the Previous Calculated Value. I do this until a new Key appears and start all over again. In addition, it needs to be reduce based on the calculation Order.

Key	Calculation Order	Sales	Value	New Sales	New Sales X Value
A	1	1000	3%	1000	30
A	2	1000	2%	970	19,4
A	3	2000	4%	950,6	38
B	1	2000	2%	2000	40
B	2	2000	2%	1960	39,2
B	3	2000	4%	1920,8	76,8
B	4	2000	2%	1844	36,8
C	1	500	3%	500	15
C	2	500	4%	485	19,4

I tried with a for next loop and the previous function. But i didnt manage to find  a solution.

Someone an Idea?

Lukas

[Anil_Babu_Samineni]

What was the logic for "New sales X value"?

[luke1986]

Thats where it gets trickey. On every calculation Order = 1 You calculate Sales * Value. Thats how you get the first line in New Sales x Value. Then you change the calculation to (Sales - Previous(New Sales x Value)) * Value.

[vinieme12 · Accepted solution]

Here is your solution

LOAD Key,

    [Calculation Order],

    Sales,

    Value,

    if(Key=Previous(Key),(Peek(NewSales) - Peek(NewSalesXValues)) * Value,Sales*Value) as NewSalesXValues,

    if(Key=Previous(Key),Peek(NewSales) - Peek(NewSalesXValues),Sales) as NewSales

FROM

[https://community.qlik.com/thread/255635]

(html, codepage is 1252, embedded labels, table is @1);

[luke1986]

Thanks Vineeth, that works!

[vinieme12]

Glad to help

Cheers

V