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.
