Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

club members per month

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

Tags (1)
4 Replies
Gysbert_Wassenaar
Not applicable

Re: club members per month

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.


talk is cheap, supply exceeds demand
Not applicable

Re: club members per month

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
Not applicable

Re: club members per month

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?


talk is cheap, supply exceeds demand
Not applicable

Re: club members per month

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

;