Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
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?

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.