Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
koguma
New Contributor

Create bar chart

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.

6 Replies
Highlighted
timpoismans
Valued Contributor

Re: Create bar chart

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?
Highlighted
koguma
New Contributor

Re: Create bar chart

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.

Highlighted
timpoismans
Valued Contributor

Re: Create bar chart

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.

Highlighted
koguma
New Contributor

Re: Create bar chart

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

Re: Create bar chart

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.

Highlighted
koguma
New Contributor

Re: Create bar chart

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.