Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikviewers!
I know it sounds little confusing so I will explain you with an example.
I have the below fields,
SupplierName | PO Year | PO Month | PriceChangex | BudgetedVolume | PriceChange | Base Value |
A | 2012 | Jan | PriceChange1 | 2500000 | 0.02 | 212500.00 |
A | 2012 | Feb | PriceChange2 | 2500000 | 0 | 212500.00 |
A | 2012 | Mar | PriceChange3 | 2500000 | 0 | 212500.00 |
A | 2012 | Apr | PriceChange4 | 2500000 | 0.015 | 215687.50 |
A | 2012 | May | PriceChange5 | 2500000 | 0 | 215687.50 |
A | 2012 | Jun | PriceChange6 | 2500000 | 0 | 215687.50 |
A | 2012 | Jul | PriceChange7 | 2500000 | 0 | 215687.50 |
A | 2012 | Aug | PriceChange8 | 2500000 | 0.015 | 218922.81 |
A | 2012 | Sep | PriceChange9 | 2500000 | 0 | 218922.81 |
The base value is a calculated field i.e.,
Jan: BudgetedVolume/12+(BudgetedVolume/12*PriceChange)
Feb: BaseValue(Jan)+BaseValue(Jan)*PriceChange
Mar: BaseValue(Feb)+BaseValue(Feb)*PriceChange... and so on.
How can I achieve it? The data cannot be expected to be in order, So I need to be fool proof. How can i calculate this?
Regards,
Subin Thomas
Why can't the data be expected to be ordered? You can force that on load with an order by clause afaik.
Yes, orderby I can use but. Is there any other method with which I can do this without even ordering?
Concatenate Year and the number of the Month (201201, 201202 etc) as a calculated dimension and sort on that should work I think. But fixing it in the load script is much easier imho.
Ok Gwassenaar, The ordering part is fine. But how to calculate the BaseValue? as I require?
SupplierName | PO Year | PO Month | PriceChangex | BudgetedVolume | PriceChange | Base Value |
A | 2012 | Jan | PriceChange1 | 2500000 | 0.02 | 212500.00 |
A | 2012 | Feb | PriceChange2 | 2500000 | 0 | 212500.00 |
A | 2012 | Mar | PriceChange3 | 2500000 | 0 | 212500.00 |
A | 2012 | Apr | PriceChange4 | 2500000 | 0.015 | 215687.50 |
A | 2012 | May | PriceChange5 | 2500000 | 0 | 215687.50 |
A | 2012 | Jun | PriceChange6 | 2500000 | 0 | 215687.50 |
A | 2012 | Jul | PriceChange7 | 2500000 | 0 | 215687.50 |
A | 2012 | Aug | PriceChange8 | 2500000 | 0.015 | 218922.81 |
A | 2012 | Sep | PriceChange9 | 2500000 | 0 | 218922.81 |