topic Re: Re: Suppressing zero values in QlikView

Suppressing zero values

Mon, 04 Aug 2014 21:49:08 GMT

I have two fields (Orders, Receipt ) put into a chart as an expression but it is just pulling it from a field in a table that has both of these values. Sometimes, there are orders during certain weeks for some products but other times, it could be zero.

What is the best way to make it so that they do not show when the values for those fields are 0. I tried to 'Suppress 0 values' under the presentation tab for those two specific fields but they don't do anything.

Re: Suppressing zero values

MK_QSL — Mon, 04 Aug 2014 22:13:10 GMT

suppress when value is null in dimension tab and suppress zero values in presentation tab...

try both...

Re: Suppressing zero values

avinashelite — Tue, 05 Aug 2014 04:58:04 GMT

Hi Jmathew,

Can you share your expression or app

Re: Suppressing zero values

sujeetsingh — Tue, 05 Aug 2014 05:05:43 GMT

You need to check the options first and if it again exist then you need to handle nulls in data model

Re: Suppressing zero values

Tue, 05 Aug 2014 14:05:38 GMT

Yes, both options to suppress missing and zero values are checked but I still see them. I think I know why the options are not working. It is because for each product, I have a transportation cost that is calculated and they exist whether there is an order or not.

The expression is just =orders which is the field name but they are associated to products and transportation costs so certain weeks, those products might not have orders but because they have a transportation cost, the options to suppress missing and zero values will not work. Any other idea on how to make this work?

This is an example of my chart:

Product, Orders, Receipt, Transportation_Cost

A1, 0, 0, 5

Re: Suppressing zero values

Tue, 05 Aug 2014 15:02:35 GMT

I'm using an expression (FirstSortedValue({<TransportationCost = {">0"},Location, [TransportationCost],1) to calculate the cheapest transportation cost.

What expression can I use to say if orders=0 AND receipt=0, then transportation cost should be null?

Re: Suppressing zero values

fsimoes81 — Tue, 05 Aug 2014 15:13:59 GMT

Are you tried to add Orders -={0}, Receipt -= {0} in your expression?

TransportationCost = {">0"},Orders -= {0}, Receipt-= {0}

Re: Suppressing zero values

Tue, 05 Aug 2014 16:53:34 GMT

I tried to do that and it is still showing the records which have 0 orders. Any other ideas on how to not show a record if a certain field is 0?

Re: Re: Suppressing zero values

fsimoes81 — Tue, 05 Aug 2014 17:13:39 GMT

Look attached example. On my example the expression works fine, maybe I don't understood correctly your problem

Re: Suppressing zero values

Thu, 07 Aug 2014 19:27:56 GMT

Thanks for your help Fabio but it is not exactly what I'm looking for.

Your sample does not show Product A3. I only want it to ignore the the record if BOTH order and receipt are 0.

Re: Suppressing zero values

rwunderlich — Thu, 07 Aug 2014 19:54:06 GMT

How about:

if(sum(Orders+Receipt), FirstSortedValue( [Transportation_Cost],1))

-Rob

Re: Suppressing zero values

Thu, 07 Aug 2014 20:13:47 GMT

Please see all attachments. I don't want it to show the rows that have 0 for orders AND receipts.

Re: Suppressing zero values

fsimoes81 — Thu, 07 Aug 2014 20:30:25 GMT

Try it:

FirstSortedValue({<[Total cost] = {">0"},Product = {"=Sum(Orders)+Sum(Receipt)>0"} >} [Total cost],1)

Re: Suppressing zero values

Thu, 07 Aug 2014 20:37:07 GMT

Sorry, but where do I use that expression?

Re: Re: Suppressing zero values

fsimoes81 — Thu, 07 Aug 2014 20:57:38 GMT

It depends, if you want to show like your example, you will need to use that expression in all expression.

See this example.