Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I have two date fields called 'Open Date' and 'Transaction Date'.
I want to find out how many 'Customers' have an open date and transaction date in the same MONTH, not the same day.
How can I do this?
Hi,
Use Month() function and IF condition with count.
Thanks,
Arvind
//First Step: To add new fields in your loading scripts
, Date(MonthStart([Open Date]),'YYYY-MM') as [Open Date Month]
, Date(MonthStart([Transaction Date]),'YYYY-MM') as [Transaction Date Month]
//Second Step: To add new Flag based on the new fields
, If([Open Date Month]=[Transaction Date Month], 1, 0) as [Same Month Flag]
//Third Step: Set Analysis measures:
//Customershave an open date and transaction date in the same MONTH
=Count(Distinct {$<[Same Month Flag]={1}>}Customer)
can you show me specifically?
This solution does not seem to be working. The IF condition only returns 0's, even though I know there should be 1's (matches) as well.