suppress zeros in measure

[alexpanjhc]

Hello 

I have a table with 4 dimensions and 12 measures.

this is sample data.

ID, Symbol, Exposure, Dates, Book, Tax Unrealized Loss, PO,STATUS

P _43581_337519,  FX ,  S ,26-APR-19, -21182.04656, Y, -14980000,OPEN
P _43574_328618,  FX  , L ,19-APR-19, -106800.3505, Y, 19608000,OPEN
P _43571_319990,  FX ,  L, 16-APR-19,  -8513.127381, Y, 524000,OPEN
P _43567_313750,  FX  , S ,12-APR-19, 514666.8915, N, -29150905,OPEN

P _43574_313751,  FD , S ,13-APR-19, 218623.33, N, -29150905,OPEN

I want to show the following

Symbol,  Exposure ,Qty, Total Qty

FX, S,-14980000,23998905

FX,L,20132000,23998905

Quantity =Sum({<STATUS={'OPEN'}, [Tax Unrealized Loss]={'Y'}>}PO)

total Qty=Sum({<STATUS={'OPEN'} >}PO)

Questions 1, above formula is not right, i want total Qty be the total of all of above of PO with the same symbol regardless the exposure. in this case would be the first 4 rows add up.

Question2, how do you suppress?

Originally I tried 

if (Sum({<STATUS={'OPEN'}, [Tax Unrealized Loss]={'Y'}>}PO)>0, symbol, null())

but the table will not show the error message says "invalid dimension".

so I changed to if ( STATUS= 'OPEN' and [Tax Unrealized Loss]= 'Y' , symbol, null()), that solved the problem with the suppress, however,

total qty is always the same as Quantity, which should not happen. 

so I changed the total Qty to aggr(Sum({<STAUTS={'OPEN'}>}PO), SYMBOL, STATUS)

but for the case above, where exposure can be L or S, total Qty will show null.

FX, S,-14980000,-

FX,L,20132000,23998905

I hope this is much clear.

Thank you!

[JordyWegman]

Hi Alex,

Do you mean with suppress that you don't want to see the row when the value is zero? Or if its null? There is a difference.

If you want to suppress measures, then all measures should be null, otherwise it won't be suppressed.

Jordy

Climber

[alexpanjhc]

Hi Jordy

They are all numbers and no nulls. I want to suppress only zeros.  thanks!

[JordyWegman]

Hi Alex,

If they are zeros you should have measures. Then go to 'Add-ons' in the properties of your table object and check out the box that says: 'Include zero values'

See example checked:

See example unchecked:

Jordy

Climber 

[alexpanjhc]

Jordy, I am not sure that is that easy. I tried this option and I am not getting what I want to get.

as a matter of fact, I am wondering what "include zero values" means. All measures that has a zero, it will be suppressed, or the first one measure

[JordyWegman]

Hi Alex,

'Include zero values' means that all values have to be zero / '0'. If there is a null value / '-' then it will not work. This means that you should add your if statement to all measures and return zero / '0' when not correct.

Jordy

Climber