Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
alexpanjhc
Specialist
Specialist

suppress zeros in measure

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!

 

Labels (1)
5 Replies
JordyWegman
Partner
Partner

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

Work smarter, not harder
alexpanjhc
Specialist
Specialist
Author

Hi Jordy

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

JordyWegman
Partner
Partner

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:

2019-05-02 08_21_41-Edit Script [QV1] With.png

See example unchecked:

2019-05-02 08_21_58-Edit Script [QV1] Without.png

Jordy

Climber 

Work smarter, not harder
alexpanjhc
Specialist
Specialist
Author

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 measureCapture.PNG

 

JordyWegman
Partner
Partner

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

Work smarter, not harder