Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Compare date with Today()

I am doing a bar chart that counts how many valid members are there based on the membership expiry date.

The 2 dimensions are (i) membership type, (ii) Months. The measure is the members count.

I have problem generating the chart as the members count for each type does not tally with the raw data. Any idea where goes wrong? I tried to compare the expiry date and today's date. 

Say expiration_date_gmt = '10-Feb-2019' , this is a valid member in Jan-2019 bar and Feb-2019 bar  but excluded in the Mar-2019 bar.

if (p_code='2' AND (Date#(expiration_date_gmt,'YYYY/MM/DD') > Date(today(), 'YYYY/MM/DD')), 'Ordinary',
if (p_code='3' AND (Date#(expiration_date_gmt,'YYYY/MM/DD') > Date(today(), 'YYYY/MM/DD')), 'Spouse',
if (p_code='4' AND (Date#(expiration_date_gmt,'YYYY/MM/DD') > Date#(today(), 'YYYY/MM/DD')), 'Affiliated'

6 Replies
Highlighted
Partner
Partner

Use Date(Today(),'YYYY/MM/DD') in all expression

Highlighted
Partner
Partner

use the same format in all expression. Please check Date# in the third expression.

Highlighted
Contributor
Contributor

Thanks.

Should I replaced all "Date#" with "Date" as well? I have removed the "#" from the 3rd Date(today()...)

if (p_code='2' AND (Date#(expiration_date_gmt,'YYYY/MM/DD') > Date(today(), 'YYYY/MM/DD')), 'Ordinary',
if (p_code='3' AND (Date#(expiration_date_gmt,'YYYY/MM/DD') > Date(today(), 'YYYY/MM/DD')), 'Spouse',
if (p_code='4' AND (Date#(expiration_date_gmt,'YYYY/MM/DD') > Date(today(), 'YYYY/MM/DD')), 'Affiliated'

 

Also, I checked the raw data, under the expiration_date_gmt field, the data are as follow. I also noticed that only the  three highlighted records are displayed in the bar chart. I have this feeling that the date format for expiration_date_gmt and Today() are different, hence the "greater than" condition is not working as what I want. 

expiration_date_gmt
23/1/2018 4:00::00 AM
1/6/2018 4:00::00 AM
20/8/2018 4:00::00 AM
1/10/2018 4:00::00 AM
10/11/2018 4:00::00 AM
21/12/2018 4:00::00 AM
31/12/2018 4:00::00 AM

 

Anyone has other suggestions?

Highlighted
Partner
Partner

hi 

can you post sample data it's will be easier to understand your problem and help you solve it 

Highlighted
Contributor
Contributor

I have posted the sample data for the expiration_date_gmt. Any idea how to resolve the issue?

Highlighted
Contributor
Contributor

Trying my luck again.

i want to plot a graph that shows the membership count over 12 months.
some sample of the data stored in the membership_expiry_date field is
1/1/2018 4:00:00 AM
31/12/2019 4:00:00 AM
31/12/2035 4:00:00 AM

As you can see, there are future dates. The count 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

Any idea how can i do it? I don't think I can use Today(), right? Appreciate any comments.