club members per month

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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

[Gysbert_Wassenaar]

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?

[#5]

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())

;