topic Re: Max value for dimension in Pivottable in QlikView

Max value for dimension in Pivottable

Thu, 16 Jun 2016 12:54:59 GMT

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.

Article	Description	Stock
1	Description 1	1 (maybe yesterdays Stock)
1	Description 1	2 (todays Stock)
2	Descrioption 2	3 (yesterday)
2	Descrioption 2	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:

Article	Description	Stock
1	Description 1	2
2	Description 2	2

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

Re: Max value for dimension in Pivottable

sunny_talwar — Thu, 16 Jun 2016 13:12:09 GMT

How about this?

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

Re: Max value for dimension in Pivottable

sunny_talwar — Thu, 16 Jun 2016 13:13:02 GMT

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

Re: Max value for dimension in Pivottable

Thu, 16 Jun 2016 13:22:08 GMT

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

Any idea why?

So still not working as it should

Re: Max value for dimension in Pivottable

sunny_talwar — Thu, 16 Jun 2016 13:55:41 GMT

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

Re: Max value for dimension in Pivottable

Fri, 17 Jun 2016 08:06:47 GMT

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

Re: Max value for dimension in Pivottable

Mon, 20 Jun 2016 15:23:15 GMT

Is the sample enough?

And does anyone having an idea how to do it?

Best regards,

Seb

Re: Max value for dimension in Pivottable

Wed, 22 Jun 2016 09:13:40 GMT

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.