topic Re: Set analysis syntax in QlikView

Set analysis syntax

Wed, 06 Mar 2013 13:54:09 GMT

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

Gysbert_Wassenaar — Wed, 06 Mar 2013 14:18:20 GMT

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

Wed, 06 Mar 2013 14:39:54 GMT

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

Wed, 06 Mar 2013 18:42:35 GMT

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

Thu, 07 Mar 2013 08:38:37 GMT

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

Thu, 07 Mar 2013 17:41:16 GMT

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

Thu, 14 Mar 2013 10:05:01 GMT

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

Thu, 14 Mar 2013 17:06:33 GMT

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

Fri, 15 Mar 2013 09:22:14 GMT

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