topic null values/ zero in a pivot table calculation in QlikView

null values/ zero in a pivot table calculation

Tue, 10 Apr 2012 20:25:19 GMT

i am calculating with this formula : sum(paid)/sum(prem) over 6 month (oct thru march) and doing a partial sum. The problem is where there is 0 value in month for example march has 0 for (paid), the values am, getting looking like it dividing by 5 and not 6.

Oct 2011 – 65%

Nov 2011 – 80%

Dec 2011 – 77%

Jan 2012 – 77%

Feb 2012 – 53%

March 2012 – 0%

I show the average should be 59%, however, the Six Month Average for the selected branch shows 71%

null values/ zero in a pivot table calculation

swuehl — Tue, 10 Apr 2012 23:44:03 GMT

Are you averaging the percentages or doing the calculation sum(paid)/sum(prem) for the six month period?

Could you post some sample data, like the paid and prem values for those months as INLINE table?

null values/ zero in a pivot table calculation

Wed, 11 Apr 2012 13:10:21 GMT

I am doing a calculation sum(paid)/sum(prem) for the six month period.

null values/ zero in a pivot table calculation

swuehl — Wed, 11 Apr 2012 13:18:22 GMT

Without knowing the numbers behind, it's hard to say what's going on.

sum(paid) / sum(prem) seems reasonably to me.

But you shouldn't expect as result for the six months period the average of the single months percentage values. I believe you would need to compare to a weighted average.

Re: null values/ zero in a pivot table calculation

Wed, 11 Apr 2012 13:25:12 GMT

here is a sample data and a snip pic

null values/ zero in a pivot table calculation

CELAMBARASAN — Wed, 11 Apr 2012 13:29:57 GMT

Hi,

Can you post the expression you have used for average?

Celambarasan

Re: null values/ zero in a pivot table calculation

Wed, 11 Apr 2012 13:33:40 GMT

I have 3 dimensions (Branchnum, agtwhole and a calculated one for month). the expression is sum(paidbeyond)/sum(annprem).. I thinikng it s skipping the 0 values.

Re: null values/ zero in a pivot table calculation

martinpohl — Wed, 11 Apr 2012 13:45:33 GMT

it seems to that there are no values in march.

So if QlikView counts the months there is no value for your dimension an the value is 5.

It should work if you add facts for march with value 0.

Regards

Re: null values/ zero in a pivot table calculation

Wed, 11 Apr 2012 13:47:21 GMT

Can you please explain what you mean by add fact..I am kind of new to this.

Re: null values/ zero in a pivot table calculation

martinpohl — Wed, 11 Apr 2012 13:53:57 GMT

something like

concatenate (your facts table) load distinct

0 as value,

'2012-03-01' as date,

customer,

artikel,

materialgroup

resident your facts table;

So for each facts-dimension(ID) you create a value 0 for march so that you can choose any dimension it is needed.

Regards