Max value for dimension in Pivottable

[#1]

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)
		2 (todays Stock)
2	Descrioption 2	3 (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:

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

[sunny_talwar · Accepted solution]

How about this?

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

[sunny_talwar]

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

[#4]

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

Any idea why?

So still not working as it should

[sunny_talwar]

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

[#6]

Yes its actually a dimension.

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

			KPI 1		KPI 2
Art (Dim)	Description (Dim)	Stock (Dim)	CW2	CW1	CW2	CW2
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

[#7]

Is the sample enough?

And does anyone having an idea how to do it?

Best regards,

Seb

[#8]

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.