Bar-Chart all Days in a Week from today minus 7 Days

[mmitteregger]

Hello,

is it possible to make a chart in which are all days from today minus 7 Days and fill it also with empty data if there are no datas in the load?

For example, i have data from MO - TUE and from Thursday - Sunday. But Wednesday i dont have datas. Can i add also the Day "Wednesday" also in the chart with empty datas?

Br

Mike

[Thiago_Justen_]

If I understood exactly what you want, I should say yes. Even there are no data for Wednesday you could create this type of visualization you want to.

[mmitteregger]

Hello!

Sorry, i mean in qlikview. I was in the false forum. Can anyone switch this post to the correct forum please? Thank you!

Br

Mike

[ggijben]

Hi Mike, 

To generate missing dates you will have to use a Master Calendar. You can use the script below:

For all days from today minus 7, you can use a calculated dimension:

IF(<YourDateField> <= Date(Today()-7),<YourDateField>)

Master Calendar Script:

minmax_temp:
LOAD MIN(%Date_Key) AS MinDate,
               MAX(%Date_Key) AS MaxDate
               RESIDENT Lot_Productie;
    
LET vmindate = NUM(PEEK('MinDate', 0 , 'minmax_temp'));
LET vmaxdate = NUM(PEEK('MaxDate', 0 , 'minmax_temp'));
LET vToday = NUM(Today());



TempCalender:
LOAD DATE($(vmindate) + ROWNO() -1 ) AS TempDate
AUTOGENERATE $(vmaxdate) - $(vmindate) +1;


//Create the Master Calendar

MasterCalendar:

LOAD
    Num(TempDate) 						AS %Date_Key,
    TempDate 							AS Date,
    Week(TempDate) 						AS Week,
    Year(TempDate) 						AS Year,
    Month(TempDate) 						AS Month,
    Day(TempDate) 						AS Day,
    WeekDay(TempDate) 						AS WeekDay,
    'Q' & Ceil(Month(TempDate) / 3) 				AS Quarter,
      Date(MonthStart(TempDate),'MMM-YYYY') 			AS MonthYear,
    Week(TempDate) &'-'& If(Week(TempDate)=1 AND
        Month(TempDate)=12,Year(TempDate)+1,
            If(Match( Week(TempDate), 52, 53) AND
                Month(TempDate)=1, Year(TempDate)-1,
                    Year(TempDate))) 				AS WeekYear,
    Dual(Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3),
    Year(TempDate) & Ceil(Month(TempDate)/3)) 		AS QuarterYear,
                    
    InYearToDate(TempDate, $(vToday), 0) * -1 		AS CYTDFlag,
    InYearToDate(TempDate, $(vToday), -1) * -1 		AS LYTDFlag,
    InMonthToDate(TempDate, $(vToday), 0) * -1 		AS CMTDFlag,
    InMonthToDate(TempDate, $(vToday), -1) * -1 	AS LMTDFlag,
    InWeekToDate(TempDate, $(vToday), 0) * -1 		AS CWTDFlag,
    InWeekToDate(TempDate, $(vToday), -1) * -1 		AS LWTDFlag

    
RESIDENT TempCalender 

ORDER BY TempDate ASC;

DROP TABLE TempCalender;
DROP TABLE minmax_temp;

[mmitteregger]

 Thank you! But i dont see in the Line-Chart the zero Data. I see only the Datas on which i have.

Br

Mike