topic Re: How to use a calculated value within an expression? in QlikView

How to use a calculated value within an expression?

subin6600 — Fri, 28 Sep 2012 07:51:33 GMT

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

Re: How to use a calculated value within an expression?

Gysbert_Wassenaar — Fri, 28 Sep 2012 07:54:13 GMT

Why can't the data be expected to be ordered? You can force that on load with an order by clause afaik.

Re: How to use a calculated value within an expression?

subin6600 — Fri, 28 Sep 2012 07:56:56 GMT

Yes, orderby I can use but. Is there any other method with which I can do this without even ordering?

Re: How to use a calculated value within an expression?

Gysbert_Wassenaar — Fri, 28 Sep 2012 08:58:47 GMT

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.

Re: How to use a calculated value within an expression?

subin6600 — Fri, 28 Sep 2012 11:35:34 GMT

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