topic Re: Calculate to avoid line to 0 in QlikView

Calculate to avoid line to 0

Tue, 18 Jun 2013 21:40:39 GMT

Hi all,

I have a question : How can I calculate the CA between 2 years ?

2013, CA 2012, CA

2013, 100 2012, 0

2013, 200 2012, 100

2013, 500 2012, 800

2013, 200 2012, 400

2013, 0 2012, 200

Sum(CA - 2013) = 200 + 500 + 200 = 900

Sum(CA - 2012) = 100 + 800 + 400 = 1300

IF CA=0 for 2012 or 2013, I don't want to include into my expression.

How can achieve that in set analysis or P() / E() function ?

Thank for your help !

Re: Calculate to avoid line to 0

Tue, 18 Jun 2013 21:58:58 GMT

Something like:

SUM({<CA -={0}> CA}

Will exclude the zero values.

Re: Calculate to avoid line to 0

Tue, 18 Jun 2013 23:32:50 GMT

Thanks Roger but it is not exactly ...

Data:

LOAD * Inline [
Site, Year, CA
1, 2013, 100
1, 2012, 500
1, 2012, 800
1, 2013, 700
2, 2012, 0
2, 2013, 200
3, 2012, 600
3, 2013, 0
4, 2012, 500
4, 2013, 900
];

Year, Site, CA Year, Site, CA

2013, 1,100 2012, 1, 500

2013, 1, 700 2012, 1, 800

2013, 2, 200 2012, 2, 0

2013, 3, 0 2012, 3, 600

2013, 4, 900 2012, 4, 500

CA 2012 = 500 + 800 + 500 = 1800

CA 2013 = 100 + 700 + 900 = 1700

SUM({<CA -={0}>} CA) is not exact because I don't want the line 2013,2,200 and 2012,3,600.

-={0} Exclude only 0. It doesn't work.

Re: Calculate to avoid line to 0

Tue, 18 Jun 2013 23:44:17 GMT

I find a solution. But It is not dynamic. I know the site to exclude : client 2 and 3.

='CA 2012 : '& Sum({<Year={$(=Max(Year))}, Site={'*'}-{2}>} CA)

='CA 2012 : '& Sum({<Year={$(=Max(Year)-1)}, Site={'*'}-{3}>} CA)