Set Analysis combination

[Jonathan_Alm]

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' "

[#2]

Jonathan,

I would do:

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

Fabrice

[Jonathan_Alm]

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.

[#4]

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

[CELAMBARASAN]

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

)

[Jonathan_Alm]

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.

[CELAMBARASAN]

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

)

[#8]

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)

)

[#9]

Just an addition,

I came across something In set analysis, that

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

Thanks,

Angad

[Jonathan_Alm]

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.