null values/ zero in a pivot table calculation

[#1]

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%

[swuehl]

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?

[#3]

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

[swuehl]

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.

[#5]

here is a sample data and a snip pic

[CELAMBARASAN]

Hi,

     Can you post the expression you have used for average?

Celambarasan

[#7]

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.

[martinpohl]

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

[#9]

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

[martinpohl]

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