Calculate how many days an event occur in a month

[ciphergod]

Hi all, need some help or directions on how to achieve something.

Current i am trying to keep track of how many days a certain event occur, across different months. This isn't such a huge issue, until i face this scenario which 'breaks' what i am currently doing.

the 3 field that are in question, Event, Start Date, End Date.

Usually i will just calculate the days between both dates and peg it onto the month in the Start Date. i.e. Event occur from 16-18 Jun --> 3 days in Jun

Issue occur when the event happens across two months, i.e. Event occur from 30 Jun-2Jul --> from what i have been doing it will be 3 days in Jun, instead of 1 day in Jun and 2 days in Jul.

There must be a better way of doing this as a whole, or maybe a formula to better represent what i want to express, any ideas?

The ultimate goal is to sum up all occurance (e.g. 12 days in Jun, 14 days in Jul ... ... ) to see the statistics across different months. 

[Vegar]

I would consider using an interval match to link your transaction table to your calendar.

Take a look at this help page: https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

It will look something like this

IntervalMatch ( DateID) //date key in master calendar

LOAD [Start date] , [End date] 

Resident EventTable;

I hope this chortle be of help for you. 

[ciphergod]

Hi, IntervalMatch does seem useful. It does require a 3rd Date related field, which i dont have and you suggest a master calendar date key.

However i have not worked with master calendar before, a quick search tells me i need to create it using an existing Date related field. In this situation, how do i do it? In the sense that do i use the start or end date for that?

In the example from the link, it basically have the field they want it to be match, and a fixed set of range. However in my case, i have a fix set of fields (12 months of the year) and an increasing entries of range, will that work too?

[Vegar]

Consider the script below. It will enable you to calculate the no of days in an event like I've done in my image bar chart. 

SET DateFormat='YYYY-MM-DD';
EventTable:
LOAD * Inline [
EventID,Start date, End date
001, 		2020-01-01, 2020-02-29
002, 		2020-01-01, 2020-03-01
003,		2020-06-16,	2020-06-18
004,		2020-06-30,	2020-07-02
005, 		2020-02-01, 2020-06-16
];

/*Defining the min and max of calendar. 
  I set them static, but you can do it more dynamicly by looking att your previous loaded values in the data model. */
LET vL.MinDate= num(date#('2020-01-01', 'YYYY-MM-DD') ); 
LET vL.MaxDate=num(date#('2020-08-01', 'YYYY-MM-DD'));

[Master calendar]:
LOAD 	
	%DateID,
	DayName(%DateID) as Date,
	Month(%DateID) as Month,
	Year(%DateID) as Year
;
LOAD 
	$(vL.MinDate)+IterNo()-1 as %DateID
AutoGenerate 1
While $(vL.MinDate)+IterNo()-1 < $(vL.MaxDate);

LEFT JOIN ([Master calendar])
IntervalMatch (%DateID) //date key in master calendar
LOAD [Start date] , [End date] 
Resident EventTable;

You can adjust the fields of your master calendar to meet your needs. Just do a google search for Qlik Master Calendar and you will find a lot of examples. 

[ciphergod]

Hi, starting to see how this could work. However i face 2 issues when trying to do something almost exact the same as your example.

1. I face the issue of syn key for Start Date and End Date

2. When i count(Date), it gives me inaccurate result. It basically throws out all available days in the range i set (the whole year) and the result i want. i.e. if result for June is 4,it will give me 4+30. Any idea what is causing that? I basically used almost the exact code you showed with some additional columns that gives other details.