Excluding Value from Set Analysis

[Anonymous]

Hi

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

[#2]

Hi Hussain

Try this hope it will reach your achievement

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

[#3]

or you can use this one also

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

[Anonymous]

Hi

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....

[jerem1234 · Accepted solution]

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!

[Anonymous]

Thankyou!