Display Month Dimension Chart (even when no data exists)

[sturmeyp]

Hi All

There are various posts relating to my question but thus far haven't been able to find a solution!

I need to create a line/bar chart which has one measure and one dimension (the month)
I always want the chart to show a static 12 months (Jan-Dec) even when there is no actual data for a given month.  

My data is organised as follows

Item	Value	Month	Year
Item #1	928	Jan	2019
Item #2	562	Jan	2019
Item #3	22	Feb	2019
item #4	881	Feb	2019
Item #5	900	Feb	2019
Item #6	101	Mar	2019
Item #7	402	Mar	2019

This is how is shows currently but I want to show full 12 months (even with no data)

Any help much appreciated

Paul

[martinpohl]

Hi!

You can't show what is not existing.

You have to create dummy values for each month to have an existing value to show.

For example

Dummy_datas:

load

month(makedate(2019,rowno())) as Month,

'2019' as Year,

0 as Value

autogenerate (12);     // 12 lines for 12 months

left join distinct 

Item     // all Items in your data if you want to select

from your_table;

concatenate (your_table)      // add them to your existing datas

load * resident Dummy_datas;

drop table Dummy_datas;