Max within set analysis

[#1]

I have a pivot table with dimensions 'Wares', 'Year-Week' and two expressions:

Max(Date)

Sum({$<Date = {"$(=Max(Date))"}>} [Amount])

The first expression (for test aid) determines the date of last week day and was calculated correctly. The second expression is calculated correctly only for the latest week, for previous weeks it is always eqval zero.

I need to see Amount for every last day of the week.

What is wrong in second expression?

Best regards!

[mike_garcia · Accepted solution]

Hi,

The Max(Date) Function will result in only one value, hence the expression will give you the amount for that date only.

What you need to do is create an array of values that correspond to the last day of every week. There can be several ways to accomplish this, depending on the available fields you have in your data model.

If you don't have it already, I would suggest to have a field created with the function weekday() that will assign the name of the day for every date. Then, if you want to sum the amount of all, say, Friday's, you could have an expression like:

sum({$<DayName = {'Fri'}>} Amount)

Note: The names of every day will be assigned as defined on the SET DayNames sentence at the beginning of the script.

Hope this helps.

Mike.

[#3]

A lot of thanks, Mike!

Your idea helped me to solve a problem. 🙂