How to show future Dates in Line Chart

[Aditya_Chitale]

Hi All,

I have a date field containing Max date upto 1 Apr 2022. My requirement was to show all YTD months in line chart (Apr 2022 to Mar 2023). If value for any particular month is not available, it should show 0. But the Month name should be constantly seen on chart.

Every Date has a project name mapped to it.

Project	Date
ABC	04-01-2022
ABC	04-02-2022
XYZ	04-03-2022
XYZ	04-04-2022

So far I have generated future dates till 31 Mar 2023 and added those as month in my chart. But when I select any project from filter, the chart shows only available month data, as project is not mapped for future dates.

Is there any way to freeze the future months and show them as 0 on filter selection ? Tried nullifying project name but didn't get what I wanted to achieve.

Regards,

Aditya

[vinieme12 · Accepted solution]

raw:
LOAD
Project,date#(Date,'DD-MM-YYYY') as Date,100 as Val,'fact' as type Inline [
Project,Date
ABC,04-01-2022
ABC,04-02-2022
XYZ,04-03-2022
XYZ,04-04-2022
];

tempDates:
Load Distinct Project Resident raw;
Left Join(tempDates)
Load Date(date#('01-04-2022','DD-MM-YYYY')+iterno()-1,'DD-MM-YYYY') as Date
Autogenerate(1)
While date#('01-04-2022','DD-MM-YYYY')+iterno()-1 <= date#('30-04-2022','DD-MM-YYYY')
;

Concatenate(raw)
Load *,'dummy' as type Resident tempDates;

drop table tempDates;

[vinieme12]

There must be possible dimension values even for dummy rows, especially dimensions which are uses for filters or aggregation in charts

add dummy dates for all possible Project values

[Aditya_Chitale]

@vinieme12 

Thanks! the forced left join did the trick

Regards,

Aditya

[vinieme12]

its a Cartesian