topic Re: exclude 0 from a metric field but at the same time get the data values for other expressions.. in QlikView

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

RheaQv12 — Mon, 03 Feb 2020 20:10:47 GMT

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

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

alex00321 — Wed, 29 Jan 2020 06:44:12 GMT

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!

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

RheaQv12 — Wed, 29 Jan 2020 10:06:07 GMT

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…

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

alex00321 — Wed, 29 Jan 2020 12:43:42 GMT

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

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

Kushal_Chawda — Wed, 29 Jan 2020 12:55:31 GMT

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.

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

RheaQv12 — Wed, 29 Jan 2020 20:04:18 GMT

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

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

Brett_Bleess — Mon, 24 Feb 2020 16:25:20 GMT

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