Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
I guess try transaction instead of Cust_ID
U mean like this?
Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))'}, TRANSACTION_ID = {"=Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {'>=$(=Num(MonthStart(Max(DateNum), -2)))<=$(=Max(DateNum))'}>} TRANSACTION_ID) > 0"}>} CUST_ID)
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)
Ur solution finally works prefectly as what i was expecting .
Something extra i want to ask you. I want to create a MTD label - like: You are looking at Jan 2017 - 31 Jan 2017 .
How to write that in text object?
May be like this:
Date(MonthStart(Max(DateNum)), 'MMM YYYY') & ' - ' & Date(Max(DateNum), 'DD MMM YYYY')
Sunny, i think i want to use as today's date. because maxdatenum for my case the date is until march. so not accurate.
how to write today's date??
oops. i know bro. thanks! is today() !
Sunny,
1 more set analysis - same as below but condition is any sales transaction in last 30 days. where should i change at below?
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)
Rgds
Jim
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), 0)))<=$(=Max(DateNum))'}>} TRANSACTION_ID) > 0"}>} CUST_ID)