I have timestamped data I want to show on a bar chart, with weeks as dimension.
The data corresponds to the amount of time an application is not running, with each entry being a date, a duration, and the application's name.
I have on a separate table the amount of time an application should be available every week.
I created a calendar to have every day, so that the dimension of the graph has no holes, in case an application did not not run for an entire week.
I have my graph, where I show the Unavailability as taking part of the maximum availability in a stacked bar, meaning the bars always add up to the max availability. ( Unavailability + ( Max availability - Unavailability ) )
I also want to be able to filter per application, and all applications are not expected to run the same amount of time every week. (Some are just 9-5, 5 days a week ; some are 24/7)
I have made everything in this QVF, but the problem is that for weeks where there is no data, Qlik show no bar instead of Max Availability, and I cannot seem to solve this.
// We need input all the dates in Fact Table with a Max Availability at least:
// In first, we need the real range dates: [TMP Calendar]: LOAD Floor(MonthStart(Min(Date))) as MinDate, Floor(MonthEnd(Max(Date))) as MaxDate FROM [lib://Folder/test file.xlsx](ooxml, embedded labels, table is Feuil1) Where Len(Trim(Date)) > 0;
// Lets do the simple Calendar:
// Calendar Variables Let vDateMin = Num(Peek('MinDate', 0, 'TMP Calendar')); Let vDateMax = Num(Peek('MaxDate', 0, 'TMP Calendar'));
// Clean Drop Table [TMP Calendar];
// Straight Calendar: Calendar: LOAD Date($(vDateMin) + IterNo()) as Date, Week($(vDateMin) + IterNo()) as Week, Year($(vDateMin) + IterNo()) as Year, Month($(vDateMin) + IterNo()) as Month AutoGenerate 1 While IterNo() <= $(vDateMax) - $(vDateMin) +1;
// Now you need all the dates in Fact Table. Lets LOAD All Dates in Fact: [TMP Fact]: LOAD [Date] Resident Calendar;
// Now we can Force a Cartesian Product there with Max Availability for ALL DAYS have a least one Application and one Max avaiability: Join ([TMP Fact]) LOAD Data as Application, "Expected Total" as [Max availability] FROM [lib://Folder/test file.xlsx](ooxml, embedded labels, table is Feuil1) Where Len(Trim(Data)) > 0;
// Lets LOAD the main date in table: Left Join ([TMP Fact]) LOAD Date(Floor(Date)) as Date, Random_Number as Unavailability, Random_Letter as Application FROM [lib://Folder/test file.xlsx](ooxml, embedded labels, table is Feuil1) Where Len(Trim(Date)) > 0;
// Now if you need a little trick. We can sumarize the Unavailability per Application and Day: Fact: LOAD Date, Application, Max([Max availability]) as [Max availability], Sum(Unavailability) as Unavailability, Max([Max availability]) - Sum(Unavailability) as Availability // We can calculate in script to performance Resident [TMP Fact] Group By Date, Application;