Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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.