topic Re: Missing dimension values in bar chart in QlikView

Missing dimension values in bar chart

Wed, 29 May 2013 11:21:08 GMT

Hi all,

I have the following three tables

- Items, with fields like Number and Priority

- Dates, with fields Number, Date, IsCreatedOn, IsClosedOn (contains one entry for each day each item was open)

- MasterCalendar, with fields like Date and MonthYear (e.g. "June 2013")

I want to create a bar chart with the sum of priority 1 items for each month of the current year. There are very few such items in my database, currently there is only one in January 2013, while all other months since have none.

The calculated dimension is: '=IF(Year(Today(0)) = Year, MonthYear)'.

The expression is: 'Sum({$<Priority={1}>} IsCreatedOn)'.

'Show all values' (in Dimension tab) is enabled and 'Suppress Zero-values' (in Presenation tab) is disabled. No other options have been changed in the chart properties.

The result is a bar chart with only three dimension values: Jan 2013 (1), Feb 2013 (0), Mar 2013 (0), although my data includes items from April and May as well. When I change the dimension definition to only '=MonthYear', I see all months up until December 2013, but also all months from the previous years, which I don't want.

When putting in the formulas into a list box, it works fine:

MonthYear	Sum(...)	Year(Today())	Year	=IF(Year(Today(0)) = Year, MonthYear)
Jan 2013	1	2013	2013	Jan 2013
Feb 2013	0	2013	2013	Feb 2013
Mar 2013	0	2013	2013	Mar 2013
Apr 2013	0	2013	2013	Apr 2013
May 2013	0	2013	2013	May 2013

I tried some things and found that, even when I change the expression to '1 + Sum(...)' the bar chart still shows only January (2), February (1) and March (1).

Can anyone please suggest why I get this strange result? Or even better, is there some way to always show all twelve Months of the current year in the Dimension, even those in the future for which no records exist yet?

Thanks and regards,

Robert

Re: Missing dimension values in bar chart

tresB — Wed, 29 May 2013 11:58:17 GMT

i went through your last para. for that you will get one check 'Supress Zero-Values' which is by default checked, you have to un-check that. Hope this helps.

Thanks.

Re: Missing dimension values in bar chart

Wed, 29 May 2013 12:52:10 GMT

Hi tesesco,

I already did that, as written in the third paragraph 🙂 But even with that option disabled, it shows only some zero values (Feb and March) while others (April and May) are still seemingly suppressed.

Thanks,

Robert

Re: Missing dimension values in bar chart

tresB — Wed, 29 May 2013 13:01:49 GMT

in that case, it might be something like NULL value or NO Value issue. Can you please upload your sample application?

Thanks.

Re: Missing dimension values in bar chart

Wed, 29 May 2013 16:26:53 GMT

Hi,

You have disabled tab "Presentation" option "Hide Missing"?

Rebeca

Re: Missing dimension values in bar chart

Thu, 30 May 2013 06:57:51 GMT

Hi tresesco,

Thanks but I'm not sure about that. As I've tried to show in the table in my original post, the expression does return a real 0 (integer) for all months with no prio 1 records, not NULL or NO value. And as I also pointed out, even when I added 1 to all sums, two months (Apr, May) that then have an expression value of 1 do not appear in the chart.

Furthermore, the issue goes away when I change the calculated dimension from =IF(Year(Today(0)) = Year, MonthYear) to just =MonthYear, so I guess it has more something to do with that, although I have no idea why.

I will try to create a sample application and upload it here later.

Thanks and regards,

Robert

Re: Missing dimension values in bar chart

Thu, 30 May 2013 07:01:23 GMT

Hi Rebeca,

Thanks. Yes, this option is disabled, but switching it on or off does not change anything. Please also see the response I just gave to tresesco's post.

Thanks and regards,

Robert

Re: Missing dimension values in bar chart

tresB — Thu, 30 May 2013 07:05:18 GMT

Have you been able to create the sample application?

Thanks.

Re: Missing dimension values in bar chart

Thu, 30 May 2013 09:18:48 GMT

Hi,

Please find my sample application attached. Please click through the values of 'Area' and note the missing months when selecting 'EMEA'.

Thanks and regards,

Robert

Re: Missing dimension values in bar chart

tresB — Thu, 30 May 2013 09:39:50 GMT

While you put :

Sum( {$<Priority= {1}>IsCreatedOn}

it filters out the months with other Priority from your dimension.

Instead Use:

Sum(if(Priority=1,IsCreatedOn))

PFA.

Thanks.

Re: Missing dimension values in bar chart

Thu, 30 May 2013 10:24:18 GMT

Thank you so much. This has actually helped me understand much more about QV now. Up until now I thought the two expressions meant basically the same and I always used the first one for easier readability...