Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sayanpwc
Partner
Partner

qlikview calculation problem

Hello freiends,

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

source.PNG

What I need to show in a table is like below

.output.PNG

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
sunny_talwar

Try this expression

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

View solution in original post

6 Replies
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
Partner
Partner
Author

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

sunny_talwar

Try this expression

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

sayanpwc
Partner
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?

output.PNG

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
Partner
Partner
Author

Thanks a lot Sunny!!