topic Re: Total of all rows in pivot in App Development

Total of all rows in pivot

Aspiring_Developer — Wed, 23 Mar 2022 08:32:06 GMT

Hello All,

I have below table:-

I have to add a new column named 'Average ' using below calculation :-

Ethernet - issueno (15+4)= 19 -- this is total

Open wkg days (76+88)= 164-- this is total

average - 15+19 / 76+164- new column to be added

Can anyone please help me how we can do it ?

Thanks in advance

brunobertels — Wed, 23 Mar 2022 12:04:19 GMT

may be this :

aggr(count ( Total<CATEGORY> ISSUE_NO),CATEGORY)

aggr(count(TOTAL<CATEGORY> OPEN_WKG_NO),CATEGORY)

Aspiring_Developer — Wed, 23 Mar 2022 13:18:55 GMT

Thanks for your quick response.

I have create below expression:-

aggr(sum(OPEN_WKG_DAYS),CATEGORY) (numerator)

aggr(count(ISSUE_NO),Category_EMS) (demoninator) - giving incorrect results

For numerator (73+72) = 145 which is correct

But for denominator (18+3)=21 should come, but is giving me 527 because of the null values present in dimensions.

I tried to supress my null values in backend like below but still in my denominator it is taking those values :-

Can you please help ? Not sure , how should I exclude null values from Count expression.

Thanks in advance

brunobertels — Wed, 23 Mar 2022 14:48:23 GMT

first check that option exclude null value in the dim of your cross table are empty

Then try in your load script to replace

not match(CATEGORY,'-') by not isnull(CATEGORY) also replace

not match(AGE_BAND_WKG,'-') by not isnull(AGE_BAND_WKG)