Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Specialist
Specialist

MonthToDate Set analysis to count no. active customers - with condition.

Hi guys,

I have created MTD set analysis to count customers as below:

Count(distinct{<Year=, Month=, Quarter=, Week=, Month=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} CUST_ID)

Now, client wants a KPI Object - A MTD(Month To Date) set analysis to count no. of ACTIVE customers.

Condition of ACTIVE customer is - MUST HAVE at least 1 sales transaction within 3 months.

Example Scenario: suppose MTD Customer count period is 1 Jan 2017 - 31 Jan 2017. so, the no. of active of customer should count within this 3 months - Jan2017, Dec 2016, Nov2016.

Pls help guys.!

Rgds

Jim

1 Solution

Accepted Solutions
sunny_talwar

This:

Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))'}, CUST_ID = {"=Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum), -2)))<=$(=Max(DateNum))'}>} TRANSACTION_ID) > 0"}>} CUST_ID)

View solution in original post

35 Replies
sunny_talwar

May be this:

Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))}, CUST_ID = {"=Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum), -2)))<=$(=Max(DateNum))}>} CUST_ID) > 0"}>} CUST_ID)

jim_chan
Specialist
Specialist
Author

is you again bro! erm... quite a long set analysis bro.

jim_chan
Specialist
Specialist
Author

but an active customer must have at least 1 sales transaction within 3 months.  i can see there is cust_id > 0 ?

how is that relate to active customer must have at least 1 sales transaction within 3 months?

sunny_talwar

Have you given it a try? Just see if it works and I can explain what it is doing

jim_chan
Specialist
Specialist
Author

not working. given me no result.  something missing in the set analysis?

sunny_talwar

Yes, my bad... missed two single quotes... try this:

Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))'}, CUST_ID = {"=Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum), -2)))<=$(=Max(DateNum))'}>} CUST_ID) > 0"}>} CUST_ID)

jim_chan
Specialist
Specialist
Author

were you testing me??? maybe you are....

sunny_talwar

Testing you?

I am not... is this not working again?

jim_chan
Specialist
Specialist
Author

It works finally. but looks weird in numbers. but anyway, i just want to know why cant's i just transcation_id > 0??

i have transaction_id , so i thought the formula should be somethin like - count cust_id where has  more than 1 transaction ID within 3 months ?

not something like this?