Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;