Compare date with Today()

[koguma]

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'

[harishicon]

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

[harishicon]

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

[koguma]

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?

[lironbaram]

hi 

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

[koguma]

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

[koguma]

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.