Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
What was the logic for "New sales X value"?
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.
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);
Thanks Vineeth, that works!
Glad to help
Cheers
V