Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results 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.

ArticleDescriptionStock
1
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:

ArticleDescriptionStock
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,

Seb

1 Solution

Accepted Solutions
MVP

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

7 Replies
MVP

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

MVP

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

=FirstSortedValue(Stock, -Date)

or

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

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

Not applicable
Author

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

Any idea why?

So still not working as it should

MVP

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
Author

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) CW2 CW1 CW2 CW2 KPI 1 KPI 2 1 Test 1 1 (todays value) 100 95 10 8 2 Test 2 2 (todays value) 90 16 1 2

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,

Seb

Not applicable
Author

Is the sample enough?

And does anyone having an idea how to do it?

Best regards,

Seb

Not applicable
Author

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.

Community Browser