topic Supress Zero Values Problem in QlikView

Supress Zero Values Problem

AbhijitBansode — Fri, 01 Jul 2011 07:41:42 GMT

I have a basic requiremnt of showing values in pivot chart when expression returns 0.

E.g. If Expression of pivot table is Sum(Amnt) and for certain selection this return 0. then QlikView will diaplay the message

'No data To Display'. But my requiremnt is to show the data in pivot table with 0 as expression value.

Please find attched excel sheet for the details of the problem. also atteched is the sample application in which i'm trying to get the same.

Supress Zero Values Problem

kaushiknsolanki — Fri, 01 Jul 2011 07:52:53 GMT

Hi,

All you have to do is go to properties -> dimensions -> click on product and check Suppress when value is null and show all values.

Hope this will help you.

Regards,

Kaushik Solanki

Re: Supress Zero Values Problem

AbhijitBansode — Fri, 01 Jul 2011 12:13:31 GMT

Thanks for reply Kaushik,

but i cant apply the settings given by you . this is showing one extra column with value 0 in pivot table.

also there can be Product selection in a list box similar ti time selection.. in that case 'Show All Values' will be incorrect.

any idea?

Re: Supress Zero Values Problem

Fri, 01 Jul 2011 14:47:50 GMT

Hi,

I think that only method to solve your problem are to change your script.

With you generate others records with 0 values on field "ShipingQuantity" and after concatenate with you table "Fact", the result show all values.

See Attachments

Supress Zero Values Problem

Fri, 01 Jul 2011 18:47:52 GMT

Hi Sparten B.

Try this option too.

Go to Presentation Tab, at the bottom there is an option "Suppress Zero-Values".

Hope this help.

Regards!

Supress Zero Values Problem

nagaiank — Fri, 01 Jul 2011 19:16:32 GMT

Hi Eduardo Correa,

The changes in the script seem to have worked. But I am not able to understand how the changes helped to avoid the extra column with 0 in the pivot table. I am also not able to explain why there was an extra column.

Re: Supress Zero Values Problem

Fri, 01 Jul 2011 19:54:53 GMT

Hi,

The value "0" are demonstrate but don't are "0".

In properties / Presentation change the values of fields:

"Null Symbol" and "Missing Symbol" to "X" AND "Y"

You will see that values are null and missing.

Maybe pivot table make a joined with the values of dimensions and demonstrate the result.

Re: Supress Zero Values Problem

nagaiank — Fri, 01 Jul 2011 21:28:53 GMT

Thank you for the explanation.

Re: Supress Zero Values Problem

AbhijitBansode — Sat, 02 Jul 2011 03:59:25 GMT

Thank u all,

Eduardo Correa,
I can not change the script, bcoz calculation of shipping quantity requires some other calculations also in my case,i'm not directly using this column for expression.

Emmanuel Prieto,
I've already tried "Suppress Zero-Values" option at the bottom of Presentation Tab.
this will show zero values when no time period or product is selected.. but as soon as i select time axis, zero values are supressed.
e.g. if for 2010 and 2011 values are 0 and 1.pivot table is:

Product Time 2010 2011
Mouse 0 1
Printer 0 1

when Printer is selected pivot table displty should be:

Product Time 2010 2011
Printer 0 1

But, the current display i'm getting is:

Product Time 2011
Printer 1

I'm really stuck up at this point.

Re: Supress Zero Values Problem

Mon, 04 Jul 2011 12:27:15 GMT

Hi, do you can to post your qvw (using reduce data, scrambling some fields)?

Re: Supress Zero Values Problem

stevedark — Mon, 04 Jul 2011 12:33:05 GMT

Hi there, you should be able to do this with set analysis on the expression:

Sum({<Year=>}Amnt)

That tells QlikView to ignore the year selection when calculating the value. You can add other fields as required by comma separating them, eg:

Sum({<Year=,Month=,Date=>}Amnt)

Hope that helps.

Steve