Set analysis - Max period by customer nr.

[pamaxeed]

Hi,

i have a question regarding set analysis.

I have the following source table:

Customnr.	Country	Domizil	ValidFrom	ValidTo
1	CH	UK	01.01.2002	01.01.2003
1	CH	CH	02.01.2003	31.12.2010
1	CH	USA	01.01.2011	15.06.2014

I want to build chart with the following condition:

In my document i have a FROM and TO filter represented as calendar boxes. The FROM and TO are holding a variable, which triggers an action (Field selection) when they are get selected:  FROM <= ValidFrom and TO >= ValidTo.

In my chart i want to have the following result:

Lets suppose that my FROM and TO filter has the following values: FROM=01.01.2002 and TO=15.06.2014.

What i expect as result in my chart is the following: the MAX period (ValidFrom and ValidTo) grouped by Customernr. in this case the result should be:

Record number 3:

1

CH

USA

01.01.2011

15.06.2014

Important condition my set analysis should be grouped by Customernr.

How i can achieve it?

Thanks,

Patric

[swuehl]

First, your set analysis is calculated once per chart, not per dimension value (if that's what you intended).

Evaluating "sets" in the context of a dimension

Then, I haven't fully understood your sample. FROM=01.01.2002 and TO=15.06.2004.

Your requested result is record 3, but it's ValidTo 15.05.2014 does not fulfill your condition TO >= ValidTo, right?

I think only record 1 fulfills your FROM <= ValidFron and TO >= ValidTo condition.

If your sample has a typo and you mean TO=15.05.2014, wouldn't record 2 be the one with the max period then?

[Gysbert_Wassenaar · Accepted solution]

If I understood you correctly you don't need set analysis. Create a straight table with Customernr. as dimension and four expressions:

firstsortedvalue(Country, -ValidTo)

firstsortedvalue(Domizil, -ValidTo)

firstsortedvalue(ValidFrom, -ValidTo)

max(ValidTo)

And change the Select in Field triggers so you select FROM >= ValidFrom and TO<=ValidTo. Otherwise the only records that can match your selections are those where FROM = TO = ValidFrom = ValidTo, i.e they're all the same date..

[pamaxeed]

Yes it was a typo, sry:

but my selection will be like that: ValidTo >= 01.01.2002, ValidTo <= 15.06.2014,

as result i will get all 3 records and i want to have the max from, to from the three records depending on the customer nr.

[pamaxeed]

Thanks it works, attached the file