How to suppress records which are with zero values in the expression

[nevilledhamsiri]

Hi Friends

Please help me to avoid the records being shown with are with zero values. I have created a measure which carries zero values since data are not their to do the calculation. What I need is to avoid these records being displayed. The table created is attached & the formula used too is given below.

(SUM({<ACCOUNT_CODE={'76110','76130','76150','76170','76180','76190','76230','76250','76270','76310','76330','76350','76355','76370','76390','76400','77110','77120','77130','77150','77170','77190','77210','77230','77250','77270','77290','77310','77330','77370','77390','77410','77430','77450','77460','77470','77480','77490','77500','77510','77550','77580','77650','78130','78150','78170','78210','78230','78310','78330','78910','79110','79140','79150','79170','79190','77655','77657','77659','77661','77663','78110','77761','78120','79161'}>}MO_DR-MO_CR))*((SUM(TOTAL{<ACCOUNT_CODE={ '61310','61320','61360'}>}CR-DR))/((SUM(TOTAL{<ACCOUNT_CODE={'61110','61160','61210','61260','61270','61460','61510','61560','61740','61760'}>}CR-DR))+(SUM(TOTAL{<ACCOUNT_CODE={'61310','61320','61360'}>}CR-DR))))+SUM({<ACCOUNT_CODE={'78215','77645','77646'}>}MO_DR-MO_CR)*0+SUM({<ACCOUNT_CODE={'77611','78329'}>}MO_DR-MO_CR)*1+SUM({<ACCOUNT_CODE={'77570','78290'}>}MO_DR-MO_CR)*0+SUM({<ACCOUNT_CODE={'78401'}>}MO_DR-MO_CR)*1+SUM({<ACCOUNT_CODE={'77570','78290'},MO_CAT={'MGT_EXP','GLO_AD'}>}CAHARGE)

Thanks

Neville

[nevilledhamsiri]

Appreciate if you look in to this

Neville

[Frank_Hartmann]

can u share a sample qvw?

maybe with scrambled data?

(Settings -->DocumentProperties -->Scrambling)

[vishsaggi]

Is this a straight table. Did you supress zero values in Presentation tab of your chart properties. ? Can you share a sample qvw file to look into?

[nevilledhamsiri]

Dear Vishwarath,Frank

This Zero vales is possible only when a mall value appears in the said account code such as (0.10, 0.05) etc. If it is 0, it wont come. I have attached the qvd for your reference. Please show me the way the records  which zero balance be avoided.

Thanks

Neville

[Frank_Hartmann · Accepted solution]

see attached.

change your dimensions to this:

=if(aggr(SUM({<ACCOUNT_CODE={'76110','76130','76150','76170','76180','76190','76230','76250','76270','76310','76330','76350','76355','76370','76390','76400','77110',
'77120','77130','77150','77170','77190','77210','77230','77250','77270','77310','77330','77370','77390','77410','77430','77450','77460','77470','77480','77490',
'77500','77510','77550','77650','78130','78150','78170','78210','78230','78290','78310','78330','78910','79110','79140','79150','79170','79190','77655','77657',
'77659','77661','77663','78110','77761','78120','79161','78381','78401'}>}MO_DR-MO_CR),ACCOUNT_CODE)<1,Null(),ACCOUNT_CODE)

and

=if(aggr(SUM({<ACCOUNT_CODE={'76110','76130','76150','76170','76180','76190','76230','76250','76270','76310','76330','76350','76355','76370','76390','76400','77110',
'77120','77130','77150','77170','77190','77210','77230','77250','77270','77310','77330','77370','77390','77410','77430','77450','77460','77470','77480','77490',
'77500','77510','77550','77650','78130','78150','78170','78210','78230','78290','78310','78330','78910','79110','79140','79150','79170','79190','77655','77657',
'77659','77661','77663','78110','77761','78120','79161','78381','78401'}>}MO_DR-MO_CR),ACCOUNT_CODE)<1,Null(),ACCOUNT_CODE+1)

hope this helps

[nevilledhamsiri]

Hi Frank

This is fine.

Thanks a lot

Neville