Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friends of qlik,
I am pretty new to qlik sense and trying to do the following: In our dance club we have members with admission and release dates assigned to them. Now I want to have a line or bar chart which shows me the number of members per month. Example data looks like this:
Name | Admission | Release
Peter | 01/11/2010 |
Maria | 22/03/2011 | 01/01/2012
Tom | 30/12/2013 |
So for example for June 2011 I would expect the chart to show 2 members (Peter and Maria).
Thank you in advance!
Regards,
Andreas
See this blog post for a solution: Creating Reference Dates for Intervals.
Add something like this to the script in the Data Editor.
Table:
LOAD
Name.
Admission,
Release,
MonthStart(Admission, IterNo()-1) as Month
FROM
....source table....
WHILE
MonthStart(Admission, IterNo()-1) <= Alt(Release, Today())
;
You can then use the new Month field as dimension and count(Name) as measure in a chart.
Dear Gysbert,
Thank you for the quick reply. It helps a lot but somehow I do not get the while loop working correctly. It seems to loop over the first name only, so I get all months for Peter until today, but Maria and Tom are missing. Is there a typical error I be doing or are more details needed to find a solution?
Kind regards,
Andreas
Could be something with the dates in the source data. Can you post a small example file with source data and a qlik sense app that demonstrates the problem?
After some tries it is now working. Please find the final script (the part which I changed) below and the .xls and .qvf file attached. Thank you for the support!
[Sheet1]:
LOAD
[Name],
[Admission],
[Release],
MonthStart([Admission],IterNo()-1)as Month
FROM [lib://Qlik_Data/ClubMembers.xlsx]
(ooxml, embedded labels, table is Sheet1)
While
MonthStart([Admission],IterNo()-1)<=Alt([Release],Today())
;