topic Re: How get Zero Values for Missing data on a bar chart in QlikView

How get Zero Values for Missing data on a bar chart

sparur — Fri, 18 Nov 2011 13:05:32 GMT

Hello, guys

I have some problems with my bar chart.

I want to get zero values on X-axis for missing data.

Please see in my attached example.

How I can resolve my trouble?

Thanks for your advices

How get Zero Values for Missing data on a bar chart

Fri, 18 Nov 2011 18:26:00 GMT

in your chart select the year dimension and click on show all values

How get Zero Values for Missing data on a bar chart

sparur — Mon, 21 Nov 2011 13:04:54 GMT

Unfortunately, it's not a simple as we want...

It's not help me, because I need only 3 last years in my chart (I use a set analysis expression for it)

and If I check on this option (show all values) I will get not beautiful result

Re: How get Zero Values for Missing data on a bar chart

SunilChauhan — Mon, 21 Nov 2011 13:59:59 GMT

dimension -> select Year-> show all values check it

and supress null

presentation -> zero on bar

see the attached file for details

How get Zero Values for Missing data on a bar chart

sparur — Mon, 21 Nov 2011 19:29:39 GMT

thanks, Sunil.

but I can't use "Show all values" option, because I need view only 3 last year: 2009, 2010 and 20011 only.

Show all values - show me more older years, I don't need in it.

Somebody has any other ideas?

Re: How get Zero Values for Missing data on a bar chart

Anonymous — Tue, 22 Nov 2011 08:31:35 GMT

Maybe this is a solution for you:

If you only need the values of the last 3 years, why not add this in your script?

Let vCurrentYear = Year(today());

Let vNumberofYearsBack = 2;

Load * Inline[...

...

]

Where Year <=$(vCurrentYear)

And Year >= ($(vCurrentYear) -$(vNumberofYearsBack)) ;

But this only works if you don't need the older values at all.

Re: How get Zero Values for Missing data on a bar chart

Tue, 22 Nov 2011 12:57:46 GMT

Without changes in script.

Only a new variable and some changes in expression.

First: Create a new variable called 'V_Channel' with this expression:

=chr(39) & replace(Concat(DISTINCT Channel, '","'), '"', chr(39)) & chr(39)

Second: Change the main expression in the chart to this one:

sum({1<Year = {">=$(=varMaxYear-2)"}>} if(WildMatch(Channel, $(V_Channel)) > 0, Value, 0))

And... it works!

Regards,

Bruno

Re: How get Zero Values for Missing data on a bar chart

Anonymous — Tue, 22 Nov 2011 13:14:23 GMT

Nice solution Bruno.

I am going to save that one 🙂

Re: How get Zero Values for Missing data on a bar chart

Tue, 22 Nov 2011 13:16:38 GMT

Haha, You're welcome

Re: How get Zero Values for Missing data on a bar chart

bm59 — Wed, 14 Dec 2011 08:39:25 GMT

Hi,

I have the same problem, and I try your trick, it doesn't work for me.

You can see my exemple on attached file

The pivot table at the bottom is your trick, but the missing sales doesn't appear (July) and a column month '-' come at the right.

Can you or anyone help me? plz

Sorry for my english...

Re: How get Zero Values for Missing data on a bar chart

Wed, 14 Dec 2011 09:21:42 GMT

Hi,

Plz find the attachment it may helps you.

Thanks & Regards

Rathish

Re: How get Zero Values for Missing data on a bar chart

Wed, 14 Dec 2011 09:52:47 GMT

Hi Sparur

Hope this would help you out,

Reg,

vivek

Re: How get Zero Values for Missing data on a bar chart

rohit214 — Wed, 14 Dec 2011 10:29:03 GMT

plase find the attachment

may it helps you

Re: How get Zero Values for Missing data on a bar chart

Wed, 05 Feb 2014 17:24:40 GMT

This is a old post but it's the first hit on Google when I searched so maybe it'll still be helpful.

There's another checkbox under the Presentation tab called "Suppress Zero-Values". Unchecking this box fixed it for me.

Thanks.

-Ray

Re: How get Zero Values for Missing data on a bar chart

Tue, 25 Mar 2014 12:52:31 GMT

Thanks so much. Could not find this answer anywhere except here! Cheers.

Re: How get Zero Values for Missing data on a bar chart

Tue, 25 Mar 2014 18:06:04 GMT

Awesome! You're welcome Ben!

-Ray