Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
i need to display the last possible entry of a field in a pivot Table.
My Selection is one year, and i want to show the latest purchasing price.
I tried :
sum({<Buchungen.ZDATUM={$(=date(max(Buchungen.ZDATUM)))}>}Buchungen.EkPreis)
and i tried last() but it won't work.
I also couldn't find a solution in the community that was right for me.
I think that just taking Artikelnr as dimension within the aggr() isn't enough. Probably you need to add there more context, for example any of the period-fields and maybe more like channels, stores and so on.
In general is the firstsortedvalue() very suitable for such a task but if there are multiple different return-values available for your max-date the function will return NULL. If such a case happens very seldom (in regard to the number of changes and not for the total occurrence in the data) you could bypass it with multiple expressions within an alt() function. Means something like this:
… alt(firstsortedvalue(Preis, -Date, 1), firstsortedvalue(Preis, -Date, 2), firstsortedvalue(Preis, -Date, 3)) ...
- Marcus
I see in your screenshot you are trying FirstSortedValue(). That would have been my first bet.
See similar issues and solutions:
Hey Miguel,
thanks for your quick answer.
With the first link I am completely on the tube .. the second appeared to me in my community research also as the best approach .. however I sometimes get no result here. I checked the max (Buchungen.EkPreis) (that's my value) for checking next to it.
In theory it is super simple ... tell me the last price of the item (which is also the last booking). But somehow I can't get anywhere.
I think that just taking Artikelnr as dimension within the aggr() isn't enough. Probably you need to add there more context, for example any of the period-fields and maybe more like channels, stores and so on.
In general is the firstsortedvalue() very suitable for such a task but if there are multiple different return-values available for your max-date the function will return NULL. If such a case happens very seldom (in regard to the number of changes and not for the total occurrence in the data) you could bypass it with multiple expressions within an alt() function. Means something like this:
… alt(firstsortedvalue(Preis, -Date, 1), firstsortedvalue(Preis, -Date, 2), firstsortedvalue(Preis, -Date, 3)) ...
- Marcus