Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Last possible Value

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
Highlighted

Re: Last possible Value

Highlighted
Contributor III
Contributor III

Re: Last possible Value

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Last possible Value

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