Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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.
Any help much appreciated.
Cheers !
Load Script with comments in attach.
Cheers.
// 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;
Drop Table [TMP Fact];
/*
You can use formulas in graph like:
Availability %:
1 - (Sum(Unavailability) / Sum([Max availability]))
*/
I cannot try this at the moment but what I read pleases me a lot. This looks exactly like what I need ; I will confirm in the morning.
Cheers !
This works perfectly for that simple file.
I have tried transposing it to my actual work file, which has multiple unavailability types, and other data around those informations ; and it doesn't work there.
When I Sum(Unavailability) QlikSense gives out weird results.
Some values are counted 5, 13, 20 times, and I don't know why as they appear only once in every table I've been able to put together.