Volume by date

[davehutchinson]

Hi all.  I can see similar questions have been asked a few times but I still can't seem to get it to work in my sheet.

I've got a master table, of people with dates when they were active.

I need to create a new table with dates to show how many people were active on those dates.

Table 1 is my Calendar table:

I've been given this formula by a colleague that sets up the dates into the financial year.  i've used this table in another dash and it works fine so I think the issue might be how I'm using it in a subsequent table. This might be the issue.

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year(Addmonths($1,9)),Makedate(Year(Addmonths($1,9))-1,4,1)) AS [Year] Tagged ('$axis', '$year')
,Dual('Q'&Num(if(Num(Month($1))<=3,4,Ceil(Num(Month($1))/3)-1)),Num(if(Num(Month($1))<=3,4,Ceil(Num(Month($1))/3)-1),00)) AS [Quarter] Tagged ('$quarter')
,right(Year(Addmonths($1,9)),2)-1&'/'&right(Year(Addmonths($1,9)),2)&' P'&Num(if(Num(Month($1))<=3,Ceil(Num(Month($1))+9),Ceil(Num(Month($1))+1)-4))&'-'&Month($1) AS [Period] Tagged ('$period')
,Dual(right(Year(Addmonths($1,9)),2)-1&'/'&right(Year(Addmonths($1,9)),2)&'-Q'&Num(if(Num(Month($1))<=3,4,Ceil(Num(Month($1))/3)-1)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter')
,Month($1) AS [Month] Tagged ('$month')
,Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth')
,Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber')
,Date(Floor($1)) AS [Date] Tagged ('$date') ;
DERIVE FIELDS FROM FIELDS [Date],[BCE_Date] USING [autoCalendar] ;

Table2 is my master table of data, UIN is the ID of the person:

[SessionListReport]:
LOAD

"UIN",

"Date Session Start",

"Date Session End",

FROM [lib://AttachedFiles/RLL - SessionListReport.xlsx]
(ooxml, embedded labels, table is [22-280124]);

So I need to count the number of UINs that have active sessions on specific dates.  I've loaded the date from the calendar, then want to count people if the calendar date falls inbetween their start and end dates. This is where Qlik is saying the error is, but I'm stumped as how to create this table.

Active_Sessions_by_Date:
Load
Date.autocalendar.Period,
COUNT(If([Date.autocalendar.Period]>=[Date Session Start] AND [Date.autocalendar.Period]<=[Date Session End]),[UIN])

Resident [SessionListReport];

Can anyone help??  I've tried lots of different ways but none of them seem to work 😞

[davehutchinson · Accepted solution]

I've managed to crack it.  The last table wasn't needed.

My issue was with using the correct name of the column in the right place.

then using the final formula actually in the visual as the measure rather than creating it as a column in the data.

In my Session list Report I had to change the name of some of the columns:

e.g. "Date Session Start" as Date,

Then the measure I created to use in the visual was:

COUNT(If([Session started]>=[Date] AND [Date]<=[Session ended],[Agent name]))

[LRuCelver]

You need to create a new table linking the two tables. To do this you need to assign every date between the start and end date to the UIN. Luckily, there is a function for that: IntervalMatch()

I've written a small example that also includes handling missing start/end dates:

Data:
NoConcatenate Load
	*,
    If(IsNull("Date Session Start"), Date(0), "Date Session Start") as StartDate,
    If(IsNull("Date Session End"), Date(999999), "Date Session End") as EndDate;
Load
	UIN,
    Date(Date#("Date Session Start"), 'DD/MM/YYYY') as "Date Session Start",
    Date(Date#("Date Session End"), 'DD/MM/YYYY') as "Date Session End"
Inline [
	"UIN",	"Date Session Start",	"Date Session End",
    1,		,				15/02/2024
    2,		15/01/2024,				29/02/2024
    3,		01/02/2024,				
];


Calendar:
NoConcatenate Load
	Date(MakeDate(2023,12,31) + RecNo()) as Date
AutoGenerate 60;


LinkTable:
NoConcatenate Load Distinct
	Date
Resident Calendar;

Inner Join IntervalMatch(Date) Load Distinct
	StartDate,
    EndDate	
Resident Data;

Left Join Load Distinct
	StartDate,
    EndDate,
    UIN
Resident Data;

Drop Fields StartDate, EndDate From Data, LinkTable;