topic Re: Reduce Sales with previous calculated value in QlikView

Reduce Sales with previous calculated value

luke1986 — Sat, 01 Apr 2017 09:02:20 GMT

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

Re: Reduce Sales with previous calculated value

Anil_Babu_Samineni — Sat, 01 Apr 2017 09:22:54 GMT

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

Re: Reduce Sales with previous calculated value

luke1986 — Sat, 01 Apr 2017 09:28:38 GMT

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.

Re: Reduce Sales with previous calculated value

vinieme12 — Sat, 01 Apr 2017 09:38:44 GMT

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);

Re: Reduce Sales with previous calculated value

luke1986 — Sat, 01 Apr 2017 09:46:38 GMT

Thanks Vineeth, that works!

Re: Reduce Sales with previous calculated value

vinieme12 — Sat, 01 Apr 2017 09:48:12 GMT

Glad to help

Cheers