8 Replies Latest reply: Mar 15, 2013 6:42 AM by christian juillard

# Set analysis syntax

Hi everybody,

as far as i remember i used to script set analysis like this

sum({<DateJ={\$(=max(DateJ)) }>}  Nb)

when i write

sum({<DateJ={'31/01/2013'}>}  Nb)  it works, nevertheless i want to do so for everymonth in a pivot table so max(DateJ) should be given by month

Regards

Chris

• ###### Re: Set analysis syntax

The syntax is correct, but you have to understand that a set analysis expression creates only one set for the entire chart. The dimensions are then applied to the set. The set is not calculated per row. So if you use month as a dimension you'll see one month with a value. The month with the overall max date. You could try using a calculated dimension =dual(Month,aggr(max(DateJ),Month)). Change Month to the name of your month field.

• ###### Re: Set analysis syntax

Thank you Gisbert  you're right i forgot that point but anyway even for 1 month it doesn't work.

I tried IF as well but doesn't give much.

What i want is the value of the last day for every month. Any idea?

Chris

• ###### Re: Set analysis syntax

If you have a master calendare, you can load a new field, say EndOfMonth, that will contain 1 of the day is the end of the month, 0 if not. Then you can sum({<EndOfMonth=1 }>} Nb) as you expression.

• ###### Re: Set analysis syntax

Thank you, it could be the way to do so but actually it's a bit more complicated because i'm using a cyclic group with day, week, month, year dimension.

When it is day i want sum(Nb) but for others I just want the sum({<Date={max(date)}>} Nb)

regards

Chris

• ###### Re: Set analysis syntax

Since you have a cycle widget, you can load the last day of the week, month, year, and day (it will make the expression easier) into separate fields in the master calendar. You will need name the fields with the field names used in the cycle widget, e.g., LastDayOfDay, LastDayOfWeek, LastDayOfMonth, LastDayOfYear if your dimensions are Day, Week, Month, Year.

For your expression you will need to use the GetCurrentField to get the select dimension and use something like sum({<LastDayOf\$(=GetCurrentField(DateSelection))=1>} value).

• ###### Re: Set analysis syntax

Hi

it was a good idea but i can't use it because the value i need is not endofyear/month/week but max(year/month/week) according to the current selection.

I don't need a full week/month/year to be finished to display the result. It can be in the middle of it.

I just don't understant why sum({<max(DateFromTable2) = { \$(=max(DateSelectedTable1)) } >} Nb ) doesn't work.

Neither sum(if (max(D1) = max(D2), Nb) )

regards

Chris

• ###### Re: Set analysis syntax

The problem is that set analysis are resolved for the entire set instead of each dimension. One way around this to construct a list of all end dates by dimensions and use the set analysis on that set. So it may look like this:

=sum({<DateJ={\$(=Concat(Aggr(MaxString(DateJ), date_dimension),','))}>} Nb)

• ###### Re: Set analysis syntax

Thank you but it doesn't give result but 0.

I got back to previous proposal with LastDay, which is a useful one