Expression formula Help

[Sonnh14tpb]

Hi, I have Transaction_date and Register_date of a list of Customer ID

I just wanna ask how do i COUNT the number of Customer have transactions the same YearMonth as their Register_date's YearMonth

My idea is to show a table of

1 Dimension: Transaction_date.YearMonth,

2 Measure: 1. how many customers have transaction that YearMonth,

                        2. How many customers that Registered that month and have transaction in the same month

Thanks

[OmarBenSalem · Accepted solution]

I have an idea ! 

Let me try to explain with an exemple :

// This is your table :

Customers :

load * inline [

CustomerID, RegisterDate, TransactionDate

1, 01/01/2021 , 05/01/2021

2, 04/01/2021, 15/01/2021

3, 05/01/2021, 09/02/2021

4, 18/02/2021, 22/02/2021

];

// If I fully understand your quest, we'll have in Jan 2021 : 2 customers (1 and 2) and 1 in Fev (4)

What we do now is create a canonical date :

Temp:
load CustomerID, RegisterDate as CanonicalDate,
'Register' as FlagCanonicalDate resident Customers;

load CustomerID, TransactionDate as CanonicalDate,
'Planned' as FlagCanonicalDate resident Customers;

CanonicalTable:
NoConcatenate
Load *,month(CanonicalDate) as MonthCanonicalDate Resident Temp;
drop table Temp;

With this :

1) you can have One Date you can use in your x-axis and still be able to represent your both dates in it (the bar chart in the left in the picture below)

2) respond to ur need (nb of customers by months who had registred and did a transaction in the same month) : bar chart in the right

Dimension : MonthCanonicalDate

Measure : count( aggr( if(count( distinct FlagCanonicalDate)=count( distinct total FlagCanonicalDate),1),CustomerID,MonthCanonicalDate))

Result :

[Sonnh14tpb]

Thank you so much for your quick answer.

Although, when i try to Create a pivot table, it says "Error, Calculation Time Out". Is this because the dataset is too big?

Also,

I want to have both Year and Month for the MonthCanonicalDate, 'YYYY-MMM', how do i do that?

[OmarBenSalem]

U add the new field here :

CanonicalTable:
NoConcatenate
Load *,month(CanonicalDate) as MonthCanonicalDate,

date(monthstart(CanonicalDate),'YYYY-MMM') as YearMonthCanonicalDate

Resident Temp;
drop table Temp;