Ignoring Dimension selction for calculating formula

Wed, 20 Sep 2017 09:41:32 GMT

Dear all

I have a pretty tricky question, and even trickier to explain it.

I have a Straight table with 3 Dimension. the last Dimension is a cycling group.

The Dimensions can be switch on and off.

I do have different Levels of calculation, triggered with a variable (vStrukturlevel).

Meaning,

- Level 0 and 1 and 2 --> sum(a) / sum(b) --> KPI total of a, ignoring the dimension --> vStrukturLevel set to 0

- Level 3, 4, 5, 6 --> sum(a) / sum(b) --> KPI total of the correct amount of a and b, because vStrukturLevel set to 3, 4, 5, 6

Cases:

no Dimension activated--> Formula --> sum(a) / sum(b) = KPI --> Field result correct vStrukturlevel = 0
First Dimension activated --> Formula --> sum(a) / sum(b) --> Field result correct vStrukturlevel = 0
Only({<PlanLevel={"$(=vStrukturLevel)"}>}KPI))
Second Dimension activated --> Formula --> sum(a) / sum(b) --> Field result correct, vStrukturLevel = 3, 4, 5 ,6 (Taking the sum of a and b depending on the Level in the cycling group).
((sum TOTAL <$(=vActiveDimension)> a) / sum({< TOTAL <$(=vActiveDimension)> b)),
First and Second Dimension activated --> Formula --> sum(a) / sum(b) Field result correct, Field result correct, vStrukturLevel = 3, 4, 5 ,6 (Taking the sum of a and b depending on the Level in the cycling group).
((sum TOTAL <$(=vActiveDimension)> a) / sum({< TOTAL <$(=vActiveDimension)> b)),

Everything perfect!

The question is now, if a selection is made of one of the value in the cycling group, the KPI should ignore the selection and taking the total sum of a and b the calculate the KPI. I do know, why the result is wrong, because I do select one of the value in the dimension.

So therefore, I want to ignore the selection for my calculation. I did achieved it in set Analysis ignoring the possible selection, but then it shows the other (not selected) values as Null, but this I want not to be showed.

Anyone a great idea to help me?

Re: Ignoring Dimension selction for calculating formula

Anil_Babu_Samineni — Wed, 20 Sep 2017 10:00:56 GMT

You can use to ignore something like below

Sum({<FieldName = {"=GetCurrentField('[Group Name]')"}>} Sales)

Re: Ignoring Dimension selction for calculating formula

Wed, 20 Sep 2017 14:54:05 GMT

Thank you very much for your help.

But, this is not the question or it doesn't work.

The main question is, if I do select a value on the dimension, the calculation is correct and it displays the right line, but I just want to let disappear the line wich contents null or 0 value, I do know, why, because in one row, there is always a value, so I think I have to work with set analyses......

Re: Ignoring Dimension selction for calculating formula

sunny_talwar — Wed, 20 Sep 2017 15:09:29 GMT

Whichever expressions are not turning 0 or null need to be forced to be 0...

If(Column(1) <> 0, Expression)

Seems like Expression 3 and 4 might need this from the screenshot

topic Re: Ignoring Dimension selction for calculating formula in QlikView

Ignoring Dimension selction for calculating formula

Re: Ignoring Dimension selction for calculating formula

Re: Ignoring Dimension selction for calculating formula

Re: Ignoring Dimension selction for calculating formula