Multiple dates

[SI_NY02]

I have a table with multiple dates like

Customer_id

Open_date

Active_date

Transaction_date

Close_date

* all dates have derived Year_month.

I need to find the number of customers were open date Year_month is the same as transaction_date Year_month. 

What is the best way to do this?

[Chirantha]

Please try the following and see if it helps

Count(DISTINCT {$<[Open_date.Year_month] = [Transaction_date.Year_month]>} Customer_id)
 

[SI_NY02]

Thanks Chirantha. I need to see these results by month.  If I bring in Open_date as the first column, all other dates (even with the formula above) resolve to 0.

[Tanish]

Hello,

If all the columns you specified is in same table then you can also create a flag with If condition in the Load Editor.

For Example -

If (Open_Date  =  Transaction_Date  , 1 , 0 ) as Flag 

Then you can directly use this Flag in front end for counting those records.

Count({<Flag = {'1'}>}Distinct Customer)

Hope this will help.....🙂

[SI_NY02]

How would I write that if I want to know if the auto generated YearMonth for Open Date is the same as the auto generated YearMonth for Transaction Date? 

[Tanish]

Count(DISTINCT {$<[Open_date.Year_month] = [Transaction_date.Year_month]>} Customer_id)

[menta]

you can add a flag in your script or if you use bubbles to add data, create a new column in the editor of the table

if(Monthstart(Open_date)=Monthstart(Transaction_date),1,0) as Flag_Same_Month,

after this, you can create a graph using the YearMonth of the Transaction_Date as dimension and the formula

Count({<Flag_Same_Month={1}>} distinct Customer_Id) as measure