Create bar chart

[koguma]

I am new to Qlik Sense, hence I really appreciate any advice from anyone. I wanted to plot a bar chart that shows the membership count over a reporting period selected.

I created a Dimension (YearMonth) to allow users to select the reporting period, using this statement
=(Date(monthstart(expiration_date_gmt), 'MMM-YYYY'))

Assume that the user select Jan-2018, Feb-2018, ... Nov-2018 and Dec-2018 to see the data for 2018,
the Y-axis will be count(expiration_date_gmt) and the X-axis will be Jan-2018, Feb-2018, ... Nov-2018 and Dec-2018.

Below are some samples of the data in the expiration_date_gmt field
1/1/2018 4:00:00 AM
31/12/2019 4:00:00 AM
31/12/2035 4:00:00 AM

The computation is as follow:
Jan 2018 - membership_expiry_date > 31 Dec 2017
Feb 2018 - membership_expiry_date > 31 Jan 2018
Mar 2018 - membership_expiry_date > 28 Feb 2018
....

How can I compare the expiration_date_gmt in order to generate the bar charts? I thought I should compare the membership_expiry_date with the selected YearMonth but I am not sure how to do it. 

Also, if you have better ways of doing what I want, please let me know as well. Really appreciate any help.

Thanks.

[timpoismans]

What would be the exact purpose of the bar chart?

1. Would it be used to look at the current month and back, as in, how many subscriptions have expired.
2. Or will it be used as a sort of "prediction", as in, how many subscriptions will expire in the future months?

[koguma]

Thanks for your reply.

The purpose of the bar chart is to show the number of active members for each month yearly. Since we already capture the membership expiry date, we can generate graphs for future years.

Moving forward, I may do other charts like - how many members expire in future months

I tired to follow this https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130 but I cannot relate the example to my report.

Thanks.

[timpoismans]

It might be possible to achieve what you want through the AsOfCalendar.

If you could provide us with a sample app, it would be a lot easier for us to help you.

[koguma]

Sorry really new to Qlik sense hence not sure what you mean by sample app. Are you referring to the qvf file?

[timpoismans]

Yup 🙂

If possible, just a qvf file with some sample data which we can use to find a solution to your issue. Do note that you'll have to zip the file to upload it here, the Community still doesn't accept qvf files.
And if possible, having the file from which you are pulling the data (Excel, QVD, csv,...) would help a lot as well, 'cause we'll need to reload the script.

[koguma]

Hi

Please see attached. The data can be seen on Data Sheet. The Bar chart can be found on the Bar Chart Sheet.

For the bar chart

X axis - shows the different group, ie. G1, G2, G3...

Y axis - shows the count of members based.

I need to generate the number of valid members from Jan 2018 to Dec 2018. 

Thanks a zillion.