Skip to main content
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max value for dimension in Pivottable

Hi guys,

I hope you can help me.

I have a pivottable with several dimensions and some KPIs. My current problem ist that I have some dimensions with several values for one article because I have different values for different days. Like in the example below.

Description 1
1 (maybe yesterdays Stock)
2 (todays Stock)
2Descrioption 23 (yesterday)
2 (today)

What I need in this table is only the max value for that dimension in terms of Date. This means I would just need it as follows:

1Description 12
2Description 22

Thought that this is a easy thing to do. I tried it with this:

=sum({<Date = {$(vMaxDate)}>}Stock)

where vMaxDate =date(max(all Date), 'DD.MM.YYYY')

This did not work.

I also tried this =Max(aggr(sum(Stock),ArticleNo,Date) which also ended up in an error.

There is probably a super easy way to do it, but I dont know how yet.

Any ideas?

thank you and best regards,


1 Solution

Accepted Solutions

How about this?

=Sum({<Date = {"$(=Date(Max(Date), 'DD.MM.YYYY')"}>} Stock)

View solution in original post

7 Replies

How about this?

=Sum({<Date = {"$(=Date(Max(Date), 'DD.MM.YYYY')"}>} Stock)


Or this if each description can have a different max date:

=FirstSortedValue(Stock, -Date)


=FirstSortedValue(Aggr(Sum(Stock, Date), -Aggr(Date, Date))

=FirstSortedValue(Aggr(Sum(Stock, Article, Description, Date), -Aggr(Date, Article, Description, Date))

Not applicable

These ones give me the error "Error in calculated dimension".

Any idea why?

So still not working as it should


Oh you are using this as a dimension. Would you be able to provide a sample with expected output to help you better here?

Not applicable

Yes its actually a dimension.

Its just like the sample above but with kpis behind it. The output should be like this:

Art (Dim)Description (Dim)Stock (Dim)CW2CW1CW2CW2
1Test 11 (todays value)10095108
2Test 22 (todays value)901612

Article No, the description, the most current stock value (usually from today) and the calendar weeks are dimensions and then the KPIs.

This would be the output. The value for stock should be the most current otherwise I have several rows for one article and the Calendarweek thing isnt working correctly or lets say it does not look like it should.

Thank you and best regards,


Not applicable

Is the sample enough?

And does anyone having an idea how to do it?

Best regards,


Not applicable

This one is the correct one. The reason why it didnt work was that a parenthesis was missing in your formula.

So things are alright now.