qlikview calculation problem

[sayanpwc]

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.

[sunny_talwar]

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?

[sayanpwc]

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

[sunny_talwar · Accepted solution]

Try this expression

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

[sayanpwc]

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?

[sunny_talwar]

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

[sayanpwc]

Thanks a lot Sunny!!