topic Re: MonthToDate Set analysis to count no. active customers - with condition. in QlikView

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

jim_chan — Mon, 30 Jan 2017 17:59:46 GMT

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

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

sunny_talwar — Mon, 30 Jan 2017 18:17:26 GMT

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)

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

jim_chan — Mon, 30 Jan 2017 18:18:17 GMT

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

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

jim_chan — Mon, 30 Jan 2017 18:21:39 GMT

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?

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

sunny_talwar — Mon, 30 Jan 2017 18:24:08 GMT

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

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

jim_chan — Mon, 30 Jan 2017 18:28:57 GMT

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

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

sunny_talwar — Mon, 30 Jan 2017 18:31:02 GMT

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)

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

jim_chan — Mon, 30 Jan 2017 18:31:55 GMT

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

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

sunny_talwar — Mon, 30 Jan 2017 18:37:54 GMT

Testing you?

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

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

jim_chan — Mon, 30 Jan 2017 18:38:48 GMT

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?

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

jim_chan — Mon, 30 Jan 2017 18:48:25 GMT

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?

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

sunny_talwar — Mon, 30 Jan 2017 18:54:08 GMT

I guess try transaction instead of Cust_ID

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

jim_chan — Mon, 30 Jan 2017 19:01:10 GMT

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)

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

sunny_talwar — Mon, 30 Jan 2017 19:04:02 GMT

This:

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

jim_chan — Mon, 30 Jan 2017 19:19:18 GMT

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?

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

sunny_talwar — Mon, 30 Jan 2017 19:36:06 GMT

May be like this:

Date(MonthStart(Max(DateNum)), 'MMM YYYY') & ' - ' & Date(Max(DateNum), 'DD MMM YYYY')

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

jim_chan — Mon, 30 Jan 2017 19:54:51 GMT

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??

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

jim_chan — Mon, 30 Jan 2017 19:56:09 GMT

oops. i know bro. thanks! is today() !

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

jim_chan — Mon, 30 Jan 2017 20:11:54 GMT

Sunny,

1 more set analysis - same as below but condition is any sales transaction in last 30 days. where should i change at below?

Rgds

Jim

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

sunny_talwar — Mon, 30 Jan 2017 20:13:08 GMT

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)