Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dominikkeller
Contributor III
Contributor III

Last possible Value

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.

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

3 Replies
Miguel_Angel_Baeyens

dominikkeller
Contributor III
Contributor III
Author

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.

marcus_sommer

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