Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi Jordy
They are all numbers and no nulls. I want to suppress only zeros. thanks!
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
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
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