# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Search instead for
Did you mean:
Partner

## qlikview calculation problem

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.

1 Solution

Accepted Solutions
MVP

Try this expression

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

6 Replies
MVP

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?

Partner
Author

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

MVP

Try this expression

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

Partner
Author

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?

MVP

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

Partner
Author

Thanks a lot Sunny!!