topic Re: Show Chart X Axis Values With Non Null Values in QlikView

Show Chart X Axis Values With Non Null Values

Tue, 30 Sep 2014 07:36:05 GMT

Hi,

I get from my database purchases amount and their date (dd/mm/yyyy), in the SQL I add a field named Month (Date_Format(Date),'%m') as Month)

and I have added an inline like this:

YearNames:

LOAD * INLINE [

Month, Month Name

01, January

02, February

03, March

04, April

05, May

06, June

07, July

08, August

09, September

10, October

11, November

12, December

];

In my charts the dimension is [Month Name] to make it more readable, the problem is that all of the months show up (from January to December) even if i have data only for a couple of months .

How can I solve this? (preferably in the chart and not in the script)

Attached is a simplified case of my problem.

Thanks!

By the way, I can't suppress 0 values because some of my inputs might be 0.

EDIT:

I've tried a different approach inspired by tresesc's comment:

I do need both the date and the month name from the DB, and I want to be able to choose by date, week number, month name and quarter so I did the following:

in the select statement for the actual data, one of the things I selected was:

DATE_FORMAT(DateColumn, '%d/%m/%Y') AS Date

And at then I've added a new select:

SELECT DISTINCT

DATE_FORMAT(DateColumn, '%d/%m/%Y') AS Date,

DATE_FORMAT(DateColumn, '%m') AS Month,

MONTHNAME(DateColumn) AS 'Month Name',

QUARTER(DateColumn) AS Quarter,

YEAR(DateColumn) AS Year,

CONCAT('W', DATE_FORMAT(DateColumn, '%u')) AS weekNum

FROM

It solved the current problem where months that are not in the data but are in the inline appear, but it had created a new one: now the chart shows the following

where the last indicator is '-' (null).

What can I do about it?

Thanks!

Re: Show Chart X Axis Values With Non Null Values

ashfaq_haseeb — Tue, 30 Sep 2014 07:44:27 GMT

like this

Have a look at calculated Dimension

=if(Amount>=0,[Month Name])

Regards

ASHFAQ

Re: Show Chart X Axis Values With Non Null Values

tresB — Tue, 30 Sep 2014 07:47:10 GMT

If you are only bothered about getting the monthnames, better option would be like:

Note: You can set the MonthNames variables to get the full names as well.

Re: Show Chart X Axis Values With Non Null Values

PrashantSangle — Tue, 30 Sep 2014 07:47:30 GMT

Hi,

Try this with calculated Dimension

if(len(trim(Amount))>0,[Month Name])

if(not isnull(Amount),[Month Name])

Re: Show Chart X Axis Values With Non Null Values

ashfaq_haseeb — Tue, 30 Sep 2014 09:37:43 GMT

Hi,

Can you close this thread by selecting appropriate answers.

Regards

ASHFAQ

Re: Show Chart X Axis Values With Non Null Values

Tue, 30 Sep 2014 09:40:09 GMT

Hi,

Not yet, I am currently trying to implement the suggestions you've given, but it has some problem i'm trying to fix.

I'll make sure to close the tread when the problem is solved.

Thanks!

Re: Show Chart X Axis Values With Non Null Values

PrashantSangle — Tue, 30 Sep 2014 09:50:11 GMT

Hi,

When you add calculated dimension.

After that check supress when value is null.

Regards,

Re: Show Chart X Axis Values With Non Null Values

Tue, 30 Sep 2014 09:58:45 GMT

Thanks,

I've tried a different approach inspired by your comment:

I do need both the date and the month name from the DB, and I want to be able to choose by date, week number, month name and quarter so I did the following:

in the select statement for the actual data, one of the things I selected was:

DATE_FORMAT(DateColumn, '%d/%m/%Y') AS Date

And at then I've added a new select:

SELECT DISTINCT

DATE_FORMAT(DateColumn, '%d/%m/%Y') AS Date,

DATE_FORMAT(DateColumn, '%m') AS Month,

MONTHNAME(DateColumn) AS 'Month Name',

QUARTER(DateColumn) AS Quarter,

YEAR(DateColumn) AS Year,

CONCAT('W', DATE_FORMAT(DateColumn, '%u')) AS weekNum

FROM

It solved the current problem where months that are not in the data but are in the inline appear, but it had created a new one: now the chart shows the following

where the last indicator is '-' (null).

What can I do about it?

Thanks!

Re: Show Chart X Axis Values With Non Null Values

tresB — Tue, 30 Sep 2014 10:03:50 GMT

In the dimensions tab, check 'Supress When Value is Null'.

Re: Show Chart X Axis Values With Non Null Values

PrashantSangle — Tue, 30 Sep 2014 10:05:29 GMT

Hi,

Did you try my reply.

Regards