Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
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.
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
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.
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
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).
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
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)
Thank you but it doesn't give result but 0.
I got back to previous proposal with LastDay, which is a useful one
Thank you for your cooperation
Regards
Chris