topic Re: Set Analysis Expression to exclude values in QlikView

Set Analysis Expression to exclude values

Sat, 17 May 2014 14:27:04 GMT

Hello,

I need help with the following 2 sceneraios:

1) I have the Pivot below and I want it to show 'only' values >500, how can I modify my formula?

I have the following set analysis expression:

If('$(vViewMode)'='Units',
Sum({$<PeriodID ={">=$(vPreviousPeriodID(18)) <$(vPreviousPeriodID(12))"},Year=,Month=,Collection-={'Others'}>}qty),0)

and this gives me the following results

2) If I have 2 columns like on the second screen shot, how can I modify the formula on the column 'Rolling 6 months' to show values where the second column 'LY Rolling 6 months>500'? (Please note once I add the column 'Rolling 6 Months' I see some 0 values on my 'LY...' column; I want column Rolling 6 months to show only if LY column >500

I appreciate your help,

Thanks,

Hugo

Re: Set Analysis Expression to exclude values

jolivares — Sat, 17 May 2014 16:07:13 GMT

Try to put inside the if...

If('$(vViewMode)'='Units' and Sum(put your sum value)>500,

Sum(put your sum value),0)

Re: Set Analysis Expression to exclude values

Sat, 17 May 2014 16:57:01 GMT

Hola Juan,

I tried as per your suggestion before and I encounter the following issue: my totals are incorrect becasue although visually I only see the values I want to see, Qlikview still adds all values and I am trying to correct that, that's why I think my restriction has to be inside the set analysis but I am not expert on QlikView....

My totals prior any attempt to exclude any values are: 123, 587

my totals after excluding values less than 500 are: 123,587 however on I don't see the 470 any more and I don't understand why my total doesn't change

Re: Set Analysis Expression to exclude values

jolivares — Mon, 19 May 2014 13:15:56 GMT

I think you need to aggregate your sum...

Sum(Aggr(Sum...( your sum)

Re: Set Analysis Expression to exclude values

Mon, 19 May 2014 15:46:03 GMT

Hi Juan,

thank you for your help, I am sorry but could you please explain where to put the sum you suggested? I am not familiar wiht the Aggr and my formula is already complex and I am having a hard time trying to figure out how to use the Aggr function on my formula.

Thanks,

Hugo