topic qlikview calculation problem in QlikView

qlikview calculation problem

sayanpwc — Tue, 10 Jul 2018 19:42:02 GMT

Hello freiends,

Need a help urgently. I have a dataset like below.

What I need to show in a table is like below

Basically I want to fetch for each material's cost for the year's max purchase date and compare the cost with its latest purchase date cost.

For ex: for material ABC the latest cost is 200 of purchase date 20/05/2018 and this cost needs to be compared with last year's last purchase date cost. i.e cost will be 150 for purchase date 16/08/2017. Now I need to compare the cost growth f these data which will be 33%.

How can I do this? Please help.

Re: qlikview calculation problem

sunny_talwar — Tue, 10 Jul 2018 19:48:52 GMT

Why is last year last purchase not on 20/11/2017? Nov comes after July, so I would expect 100 to be used in the calculation, right?

Re: qlikview calculation problem

sayanpwc — Tue, 10 Jul 2018 19:51:36 GMT

Yes. Sorry. 100 is last purchase cost as it was on 20/11/2017.

Re: qlikview calculation problem

sunny_talwar — Tue, 10 Jul 2018 19:55:44 GMT

Try this expression

=If(SecondaryDimensionality() = 0, Num(FirstSortedValue(cost, -purchasedate)/FirstSortedValue({<Year = {"$(=Max(Year)-1)"}>}cost, -purchasedate)-1, '#.##%'), FirstSortedValue(cost, -purchasedate))

Re: qlikview calculation problem

sayanpwc — Tue, 10 Jul 2018 20:08:52 GMT

Thanks Sunny. You are almost there. Just want to know how to get the representation like the output I showed?

What dimension and expressions I need to take in pivot table?

I need to show the max cost values under year fields as well another column that will tell the growth % among the years.

Output should be like this, What are my expressions?

Re: qlikview calculation problem

sunny_talwar — Wed, 11 Jul 2018 00:41:34 GMT

Have you looked at the attached qvw file? Isn't it already in this format? Can you let me know what is not right?

Re: qlikview calculation problem

sayanpwc — Wed, 11 Jul 2018 09:43:03 GMT

Thanks a lot Sunny!!