15 Replies Latest reply: Oct 10, 2018 7:13 AM by Andrey Krylov

# Set Analysis trouble

Hi all ( stalwar1)

I have a little issue with a set analysis; let me explain.

Here's my data:

Année, Mois, Semaine, Sales

2018, 3 , 13, 200

2017, 3 , 13, 250

2018, 4 , 13, 600

2017, 4 , 13, 500

2018, 5 , 14, 200

2017, 5 , 14, 250

2018, 5 , 15, 600

2017, 5 , 15, 500

];

As u can see, the main ISSUE with this is that some weeks are at the same time part of 2 Months

This part where week 13 is at the same time part of Month 3 and 4

2018, 3 , 13, 200

2017, 3 , 13, 250

2018, 4 , 13, 600

2017, 4 , 13, 500

What we aim to do is : when the user select a Week, we show the Sales of the whole Month associated to the Selected Week.

Example;

we select Week 14:

it should be as follow:

This is CORRECT using this expression : Sum({<Semaine, Mois=p({<Mois>}Mois)>}Sales)

Now, the issue as mentionned is with week 13, we should have the Sales for the min Month associated to the week; which means : Mois=3

Here's how I've done it :

Sum({<Semaine, [Année], Mois={"\$(=aggr(min(total <Semaine> Mois),[Année],Mois,Semaine))"}>}Sales)

Result: (when I select a Week, it works just AS I EXPECT)

But when I do not select a thing, it only give back ZEROS:

Why is this happening? especially when this part give back the Month it should give back (aggr(min(total <Semaine> Mois),[Année],Mois,Semaine))

I can do an if statement to correct this (to play with the selections) but I'd prefer an expression that would work no matter what.

Anay help is appreciated !

Thanks

• ###### Re: Set Analysis trouble

Hi,

Check the attached qvw file.

Regards,

Kaushik Solanki

• ###### Re: Set Analysis trouble

Thanks Kaushik, I'm aware of a solution based on the script but unfortunately this is not an option;

and besides, I don't have Qlikview installed in my post :/

• ###### Re: Set Analysis trouble

Let me try few things and see if it works in frontend.

Regards,

Kaushik Solanki

• ###### Re: Set Analysis trouble

Because set analysis is evaluated once per chart, this won't work.... right now you have multiple values per dimension for your inner aggregation... which is why when the set analysis is evaluated (aggr(min(total <Semaine> Mois),[Année],Mois,Semaine)) it is evaluated as a null. HINT: Add this to a text box object to see it's value when nothing is selected vs. when 13 is selected.

You can use Aggr with if statement to fix this instead of using set analysis here

• ###### Re: Set Analysis trouble

Here is what I think can be used

Sum({<Semaine, [Année]>} Aggr(If(Mois = Min(TOTAL <Semaine> Mois), Sales), [Année], Mois, Semaine))

• ###### Re: Set Analysis trouble

Or this

Sum({<Semaine, [Année], Mois = p(Mois)>} Aggr(If(Only({<Semaine, [Année]>} Mois) = Min({<Semaine, [Année]>} TOTAL <Semaine> Mois), Only({<Semaine, [Année]>} Sales)), [Année], Mois, Semaine))

• ###### Re: Set Analysis trouble

You Rocked Man...

Regards,

Kaushik Solanki

• ###### Re: Set Analysis trouble

He's on a whole other planet of his own

• ###### Re: Set Analysis trouble

BINGO !!

• ###### Re: Set Analysis trouble

This will give back only the sales for the Min Week:

• ###### Re: Set Analysis trouble

Try the modified version

Sum({<Semaine, [Année], Mois = p(Mois)>} Aggr(If(Only({<Semaine, [Année]>} Mois) = Min({<Semaine, [Année]>} TOTAL <Semaine> Mois), Only({<Semaine, [Année]>} Sales)), [Année], Mois, Semaine))

• ###### Re: Set Analysis trouble

This only works if u select a week; if u do not, it only takes the min week into consideration:

• ###### Re: Set Analysis trouble

Maybe this

Sum({<Semaine, [Année], Mois={"\$(=If(GetSelectedCount(Semaine) = 1, Min(Mois), '*'))"}>}Sales)