One general Time Dimension

[WM]

Hi everyone,

I am very new to Qlik Sense and I hope, that you can help me with my problem. I searched a lot on google and in this forum but I couldn't find any solution.

I have a table with 2 Date fields in it: [Date Created] and [Date Finished] 

I want to count some events via SET Analysis and put it into a Bar Chart with CalendarWeeks as the dimension. So that I can see e.g. that in CalendarWeek 09-2019, 200 events have been created and 180 have been finished.

The problem is, that those 20 events remain unfinished until they are finished in the future e.g. next CalendarWeek or even next Month.

It is also possible, that in one CalendarWeek only 140 events are created but 300 are finished, because the unfinished events of prior weeks and months get finished during that week.

When I count the finished and unfinished events and put them into a bar chart next to each other it only shows me the events that were finished during that week AND ALSO were created in that week! So events that were created prior and just in that CalendarWeek finished don't show up. As a result the "created bar" is always greater than the "finished bar"

I have already created 2 seperate MasterCalendars for [Date Created] and [Date Finished] and when I only count the created events and use CalendarWeeks of MasterCalendarCreated as a dimension it shows the correct number. Same thing when i count the finished events and use MasterCalendarFinished as a dimension in a seperate bar chart.

But as soon as I put the created and finished events as measures in one bar chart, the numbers of finished events is not correct anymore.

I also tried the CanonicalMasterCalendar, but that didn't work as well, maybe i did something wrong there.

I hope I was able to present my problem properly and I thank you in advance for any advice and solutions!

[dplr-rn · Accepted solution]

canonical calender  is the way to go. HIC blog explanation is very good

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

if you already tried and it didnt work.

share the script you used and maybe a sample app too

[WM]

Hi,

thank you for the response. Below I posted the Code I created in the DataEditor. I also created a sample App, however I couldn't find any options to export the app.

CanonicalDate:

Ident2DateCreated:
MAPPING LOAD
ID,
[Date Created]
RESIDENT Tabelle1;

Ident2DateFinished:
MAPPING LOAD
ID,
[Date Finished]
RESIDENT Tabelle1;

DateBridge:
Load
ID,
Applymap('Ident2DateCreated',ID,Null()) as CanonicalDate,
'Created' as DateType
Resident Tabelle1;
Load
ID,
Applymap('Ident2DateFinished',ID,Null()) as CanonicalDate,
'Finished' as DateType
Resident Tabelle1;

MasterCalendar for the CanonicalDate:

QuartersMapC:
MAPPING LOAD
rowno() as MonthC,
'Q' & Ceil (rowno()/3) as QuarterC
AUTOGENERATE (12);

TempC:
Load
min(CanonicalDate) as minDateC,
max(CanonicalDate) as maxDateC
Resident DateBridge;

Let varMinDateC = Num(Peek('minDateC', 0, 'TempC'));
Let varMaxDateC = Num(Peek('maxDateC', 0, 'TempC'));
DROP Table TempC;

TempCalendarC:
LOAD
$(varMinDateC) + Iterno()-1 As NumC,
Date($(varMinDateC) + IterNo() - 1) as TempDateC
AutoGenerate 1 While $(varMinDateC) + IterNo() -1 <= $(varMaxDateC);

MasterCalendarC:
Load
TempDateC AS CanonicalDate,
week(TempDateC) As WeekC,
Year(TempDateC) As YearC,
Month(TempDateC) As MonthC,
Day(TempDateC) As Day,
ApplyMap('QuartersMapC', month(TempDateC), Null()) as QuarterC,
Week(weekstart(TempDateC)) & '-' & WeekYear(TempDateC) as WeekYearC,
WeekDay(TempDateC) as WeekDayC
Resident TempCalendarC
Order By TempDateC ASC;
Drop Table TempCalendarC;

Thank you in advance.