Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this expression
=If(SecondaryDimensionality() = 0, Num(FirstSortedValue(cost, -purchasedate)/FirstSortedValue({<Year = {"$(=Max(Year)-1)"}>}cost, -purchasedate)-1, '#.##%'), FirstSortedValue(cost, -purchasedate))
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?
Yes. Sorry. 100 is last purchase cost as it was on 20/11/2017.
Try this expression
=If(SecondaryDimensionality() = 0, Num(FirstSortedValue(cost, -purchasedate)/FirstSortedValue({<Year = {"$(=Max(Year)-1)"}>}cost, -purchasedate)-1, '#.##%'), FirstSortedValue(cost, -purchasedate))
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?
Have you looked at the attached qvw file? Isn't it already in this format? Can you let me know what is not right?
Thanks a lot Sunny!!