Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Max value for dimension in Pivottable

How about this?

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

View solution in original post

7 Replies
Highlighted

Re: Max value for dimension in Pivottable

How about this?

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

View solution in original post

Highlighted

Re: Max value for dimension in Pivottable

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))

Highlighted
Not applicable

Re: Max value for dimension in Pivottable

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

Any idea why?

So still not working as it should

Highlighted

Re: Max value for dimension in Pivottable

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

Highlighted
Not applicable

Re: Max value for dimension in Pivottable

Yes its actually a dimension.

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

KPI 1KPI 2
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,

Seb

Highlighted
Not applicable

Re: Max value for dimension in Pivottable

Is the sample enough?

And does anyone having an idea how to do it?

Best regards,

Seb

Highlighted
Not applicable

Re: Max value for dimension in Pivottable

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.