Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Sonnh14tpb
Contributor II
Contributor II

Expression formula Help

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

Labels (1)
1 Solution

Accepted Solutions
OmarBenSalem

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 :

Capture.PNG

View solution in original post

3 Replies
OmarBenSalem

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 :

Capture.PNG

Sonnh14tpb
Contributor II
Contributor II
Author

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;