Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Partner
Partner

How to have an exhaustive set of values ?

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 !

3 Replies
Partner
Partner

Re: How to have an exhaustive set of values ?

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]))

*/

 

 

Support your colleagues. Remember to "like" the answers that are helpful to you and flag as "solved" the one that helped you solve. Cheers.
Highlighted
Partner
Partner

Re: How to have an exhaustive set of values ?

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 !

Highlighted
Partner
Partner

Re: How to have an exhaustive set of values ?

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.