Show Chart X Axis Values With Non Null Values

[#1]

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

    DB

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!

[ashfaq_haseeb]

like this

Have a look at calculated Dimension

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

Regards

ASHFAQ

[tresesco]

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.

[PrashantSangle]

Hi,

Try this with calculated Dimension

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

or

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

[ashfaq_haseeb]

Hi,

Can you close this thread by selecting appropriate answers.

Regards

ASHFAQ

[#6]

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!

[PrashantSangle]

Hi,

When you add calculated dimension.

After that check supress when value is null.

Regards,

[#8]

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

    DB

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!

[tresesco · Accepted solution]

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

[PrashantSangle]

Hi,

Did you try my reply.

Regards