topic Re: Set Analysis combination in QlikView

Set Analysis combination

Jonathan_Alm — Thu, 21 Nov 2013 11:11:43 GMT

Hi,

I have a chart that shows the absence time for the last 10 weeks in my Dashboard.

This is solved with the following set analysis:

=sum({

RMonth=,

CalendarYear={$(=max(CalendarYear))},

CalendarWeek={">$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek)-10)"} * {"<=$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek))"},

Absence={1}

[Absence Time]/60

)

I now have to extend this by looking at a combination of values.

There are two columns: Valid Absence and Whole Day absence. This can be set to 1 or 0, 'NO ' or 'YES'. The tables looks like this:

Valid	Whole day absence
1	NO
0	YES

My set analysis above summarizes all combinations of valid and whole day absence. There can be 4 combinations.

Comb 1	Comb 2	Comb 3	Comb 4
Valid = 1 AND Whole Day Absence = 'NO'	Valid = 1 AND Whole day absence = 'YES'	Valid = 0 AND Whole day absence = 'NO'	Valid = 0 AND Whole day absence = 'YES'

However, I need to filter 1 combination out. And that is when Valid = 0 AND Whole Day Absence = 'NO'.

So when these two values are combined, they shall be excluded.

Can someone please help me with the syntax to be able to get the summary of absence time for all combinations except "Valid = 0 AND Whole Day Absence = 'NO' "

Re: Set Analysis combination

Thu, 21 Nov 2013 11:36:06 GMT

Jonathan,

I would do:

{ {Your current set} * {{<valid={0} * [whole day absence]={'NO'}} }

Fabrice

Re: Set Analysis combination

Jonathan_Alm — Thu, 21 Nov 2013 12:41:54 GMT

Hi,

I am sorry but I can't quite follow you here. I try to incorporate all those { } but the syntax does not seem right.

Re: Set Analysis combination

Thu, 21 Nov 2013 13:01:23 GMT

Hi,

What I wanted to do is:

{your set} * {a new set}

it shoudl be an intersection because you reduce the scope. If I remember, the whole set composed of these 2 sets must be enclosed into {}. therefore sth like { {your set} * {a new set} }

The new set is also composed of the intersection of two sets: {<valid={0}>} * {<[whole day absence]={'NO'}> }

The problem with the set analysis is that they become rapidly very hard to write, read and maintain.

Fabrice

Re: Set Analysis combination

CELAMBARASAN — Thu, 21 Nov 2013 13:12:59 GMT

Try this

=sum({

RMonth=,

CalendarYear={$(=max(CalendarYear))},

CalendarWeek={">$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek)-10)"} * {"<=$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek))"},

Absence={1},

ValidAbsence-={0},

WholeDayAbsence-={'NO'}

[Absence Time]/60

)

Re: Set Analysis combination

Jonathan_Alm — Thu, 21 Nov 2013 15:03:15 GMT

Hi,

If I write the syntax like that, the set analysis will filter out all combinations where whole day absence is set to 'NO' (combination 1 and 3).

So unfortunately I cannot write it like that.

Re: Set Analysis combination

CELAMBARASAN — Thu, 21 Nov 2013 15:29:07 GMT

sum({

RMonth=,

CalendarYear={$(=max(CalendarYear))},

CalendarWeek={">$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek)-10)"} * {"<=$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek))"},

Absence={1}

> *<WholeDayAbsence-={'NO'}> * <ValidAbsence-={0}>}

[Absence Time]/60

)

Re: Set Analysis combination

Fri, 22 Nov 2013 07:41:58 GMT

Try:

=sum({

RMonth=,

CalendarYear={$(=max(CalendarYear))},

CalendarWeek={">$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek)-10)"} * {"<=$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek))"},

Absence={1}

- <Valid={0}, [Whole Day Absence]={NO}>

}

[Absence Time]/60

)

or the old tried and true IF method:

=sum({

RMonth=,

CalendarYear={$(=max(CalendarYear))},

CalendarWeek={">$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek)-10)"} * {"<=$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek))"},

Absence={1}

if( Valid<>0 or [Whole Day Absence]<>'NO',

[Absence Time]/60)

)

Re: Set Analysis combination

Fri, 22 Nov 2013 09:30:17 GMT

Just an addition,

I came across something In set analysis, that

instead of using Absence={1}, this Absence={"=1"} works.

Thanks,

Angad

Re: Set Analysis combination

Jonathan_Alm — Mon, 25 Nov 2013 10:28:59 GMT

Hi,

I could not make the expression work. It always singled out some value, not a combination of both.

Instead I solved it in the script by concatenating the columns into 1, creating 4 different "flags":

00, 01, 10, 11.

I then filtered values out that was lower than 0.

num(Valid) & num(if([Whole day absence] = 'H', 1, 0)) as AbsenceFlag,

The set analysis was then written like:

=sum({

RMonth=,

CalendarYear={$(=max(CalendarYear))},

CalendarWeek={">$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek)-10)"} * {"<=$(=max({<CalendarYear={$(=Max(CalendarYear))}>}CalendarWeek))"},

Absence={1},

AbsenceFlag ={">0"}

[Absence Time]/60

)

Thanks for all the input.