Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
Use Date(Today(),'YYYY/MM/DD') in all expression
use the same format in all expression. Please check Date# in the third expression.
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?
hi
can you post sample data it's will be easier to understand your problem and help you solve it
I have posted the sample data for the expiration_date_gmt. Any idea how to resolve the issue?