Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
johnca
Specialist
Specialist

IntervalMatch Redux

Okay, I'm throwing in the towel...I am stumped and need help with IntervalMatch.

I have data records with start and end dates, and need to display each record in a time chart. Assume the current month is October 2015.

Say my data looks like below:

IDStartEnd
16/1/20158/8/2015
26/1/2015
36/17/20156/24/2015
48/3/20159/16/2015
59/17/2015

I need a chart showing the months as dimension, then each ID showing in the months like this;

ID 1 shows in Jun, Jul, Aug

ID 2 shows in Jun, Jul, Aug, Sept, Oct

ID 3 shows in Jun

ID 4 shows in Aug, Sep

ID 5 shows in Sep, Oct

Charted as;

MonthCount
June3
July2
August3
September3
October2

Thanks!

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_189799_Pic1.JPG

QlikCommunity_Thread_189799_Pic2.JPG

QlikCommunity_Thread_189799_Pic3.JPG

QlikCommunity_Thread_189799_Pic4.JPG

table1:

LOAD * FROM [https://community.qlik.com/thread/189799] (html, codepage is 1252, embedded labels, table is @1);

tabMonths:

LOAD ID,

    AddMonths(MonthName(Start),IterNo()-1) as MonthName

Resident table1

While MonthName(AddMonths(Start,IterNo()-1))<=Alt(End,Today());

hope this helps

regards

Marco

View solution in original post

6 Replies
hic
Former Employee
Former Employee

I wouldn't use IntervalMatch for this. I would create reference dates instead. See Creating Reference Dates for Intervals

When you have done that, you can create a standard Master Calendar for the reference dates and show the count per month.

HIC

petter
Partner - Champion III
Partner - Champion III

Yes there is no need for IntervalMatch here. The "quick-and-dirty" approach would be:

2015-10-30 #6.PNG

johnca
Specialist
Specialist
Author

My bad...you both replied with exactly what I asked for, but that wasn't what I need. I asked the wrong question.

What I need is a chart that shows, by month, which ID's are "open".

If I select ID 2 then the chart should show a count of 1 for June through October.

If I select ID 4 the chart shoukld show a count of 1 for August and September.

If I select ID's 2 and 4 I should see counts of 1, 1, 2, 2, 1 for June-October resp.

If I select none (Clear) I should see a count of 3 for June, 2 for July, 3 for August, 3 for September and 2 for October.

My apologies for not being clearer.

hic
Former Employee
Former Employee

No, I think you asked the right question, because this is a data modelling problem.

If you create reference dates and a master calendar, like I suggest in my previous answer, you will have a data model with Month in the calendar table and ID in the original table. If you now create a bar chart with Month as dimension and Count(distinct ID) as measure/expression, you will get exactly what you describe.

HIC

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_189799_Pic1.JPG

QlikCommunity_Thread_189799_Pic2.JPG

QlikCommunity_Thread_189799_Pic3.JPG

QlikCommunity_Thread_189799_Pic4.JPG

table1:

LOAD * FROM [https://community.qlik.com/thread/189799] (html, codepage is 1252, embedded labels, table is @1);

tabMonths:

LOAD ID,

    AddMonths(MonthName(Start),IterNo()-1) as MonthName

Resident table1

While MonthName(AddMonths(Start,IterNo()-1))<=Alt(End,Today());

hope this helps

regards

Marco

johnca
Specialist
Specialist
Author

Hi Marco,

Thanks for this simple yet elegant solution. With your solution came the addition of learning a new function (to me) of "ALT".

I added NoConcatenate to the resident load then dropped the initial table.

Thanks again,

John