topic Re: Re: Excluding Value from Set Analysis in QlikView

Excluding Value from Set Analysis

Anonymous — Fri, 06 Dec 2013 12:01:19 GMT

I have a chart showing sales cumulative for the year - the line therefore shows Sales YTD...I have the last four years on the chart.

I use the following equation for each line/year:-

RangeSum(Above(Sum((if(FiscalYear='2011',(if(classtype='Actual',(if(linetype<>'HotRunner',(val),0)))),0))),0,NoOfRows()))/1000

In the above equation I set the Fiscal Year, the classtype and exclude values where the linetype = Hotrunner. All this works fine!

I wanted to upgrade my chart so that the last four years line always showed irrespective of what criteria had been selected elsewhere.

So decided Set Analysis was the way forward...I used the following equation to replace the above:-

RangeSum(Above(Sum({<FiscalYear={2011},classtype={Actual},linetype={'<>Hotrunner'}>}val),NoOfRows()))/1000

But this does not work...I cannot understand why - can anyone help?

All help greatly appreciated!

Regards

Sagar

Re: Excluding Value from Set Analysis

Fri, 06 Dec 2013 12:05:53 GMT

Hi Hussain

Try this hope it will reach your achievement

RangeSum(Above(Sum({<FiscalYear={2011},classtype={Actual},linetype={"*"}-{'Hotrunner'}>}val),NoOfRows()))/1000

Re: Excluding Value from Set Analysis

Fri, 06 Dec 2013 12:06:23 GMT

or you can use this one also

RangeSum(Above(Sum({<FiscalYear={2011},classtype={Actual},linetype-={'Hotrunner'}>}val),NoOfRows()))/1000

Re: Re: Excluding Value from Set Analysis

Anonymous — Fri, 06 Dec 2013 12:16:15 GMT

Thanks for your help! I tried both and it makes no difference - I am begining to wonder if it is the set analysis that is causing the issue or the rest of the equation which is causing the issue...

I have attached my file for you to see what I am trying to achieve.

If you look at the 2011 line it is flat whilst the other years which are correct go up over time. With the original formula for 2011 in place it to goes up over time yet with the set analysis it is flat....

Re: Re: Re: Excluding Value from Set Analysis

jerem1234 — Fri, 06 Dec 2013 13:39:47 GMT

See if attached meets your needs. I used Full accumulation on the expression tab instead of what you were doing and converted all the statements to set analysis. I also ignored selections made.

Hope this helps!

Re: Re: Excluding Value from Set Analysis

Anonymous — Fri, 06 Dec 2013 14:09:20 GMT

Thankyou!