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