Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
SI_NY02
Contributor III
Contributor III

Multiple dates

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?

Labels (4)
6 Replies
Chirantha
Support
Support

Please try the following and see if it helps

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

SI_NY02
Contributor III
Contributor III
Author

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
Partner - Creator
Partner - Creator

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
Contributor III
Contributor III
Author

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
Partner - Creator
Partner - Creator

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

menta
Partner - Creator II
Partner - Creator II

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