Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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)

Not applicable
Author

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