exclude 0 from a metric field but at the same time get the data values for other expressions..

[RheaQv12]

Hi All,

I have this scenario where I am trying to build a table or pivot table with the following dimensions

Date, CUSTID, Store, etc - in one of the metric I use has 0 values in the data which needs to be excluded meaning

for this metric I should only get where the value is >0 but if I do this in the dimension I loose the data values for other expressions... I want to get the other expressions without loosing the data

in the below screenshot scratched is metric which has values and I should exclude 0 for this metric for the combination of these dimensions.

when I do the if(Scratched>0) in the dimension I am loosing records for the other metrics my numbers are going down

for the other metrics - for ex: my released metric will go down from 82K to 2K

what I need to show only scratched where the value is >0 at the same time get all the values for the other metrics

can someone please help with this tricky situation... sample file is attached

[alex00321]

Hi Maybe I'm not total following, but the 4 metrics before Scratched is not related from data perspective. So if you suppress one of them, others may be impacted. Thanks!

[RheaQv12]

Hi,

Thank you, that is the issue. When I used IF (Scratched)>0 in the dimensions and use the suppress when null option in the dimension I will loose the data for the other 4 metrics...

any ideas/suggestioins…

[alex00321]

Hi, maybe you could put an expected result otherwise, I will suggest not to use Aggr, instead, just use sum. Thanks!

[Kushal_Chawda]

If you use the condition >0 for metric then it will definitely reduce the data based on condition and this is how it should work actually. I don't think what you are asking is possible because if you just want to show records with condition>0 then other metrics definitely will reduce. 

[RheaQv12]

Hi Kush,

Thank you. yeah, you are right but do you think there is any other way like using set analysis - when calculating the metrics I take ALL records some thing like  SUM({<CUST_ID={"*"}>} SALES) - for each metric then click on the suppress when null for each dimension. Its ok if we are not displaying or showing all records for the dimensions but at least we get the TOTALs

right?

thanks a lot

[Brett_Bleess]

I believe you are on the right track with Set Analysis, here is a Design Blog post that may be of some use:

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704

Here is the base URL for the site you can use to search for other items if the above does not quite get you there, as there are other posts related to Set Analysis out there...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett