topic Re: Formula for new transactions for new customers (never ordered before) in QlikView

Formula for new transactions for new customers (never ordered before)

klaus_feldam — Tue, 13 Jan 2015 19:19:25 GMT

I would be extremely happy if you could help us resolve a business problem we are trying to resolve in QlikView.

We have two calculations we are looking to make:

1. Provide a customer count of any new customers last month who has NOT ordered before.

The following formula gives us the number of customers who ordered last month:

count(DISTINCT if(MonthName(TRANDATE)=MonthName(AddMonths(Today(),-1)),CUSTOMER_ID))

Which formula would you recommend for isolating the customers who has not ordered before, i.e. no previous TRANDATE?

2. Provide a count of customers who has ordered in the past 6 months, as of end of last month (12/31), end of previous month (11/30), etc. for the past 12 months? I can certainly do that with 12 individual formulas, but for efficiency would like to use a variable to provide the rolling monthly customer count.

Any great ideas of how to do this effectively?

Your assistance is greatly appreciated.

Re: Formula for new transactions for new customers (never ordered before)

Anonymous — Tue, 13 Jan 2015 19:43:22 GMT

Hi,

for point 1) you can try

count({<TRANDATE=e({<(Month(TRANDATE)<Month(AddMonths(Today(),-1))>}TRANDATE)>}

DISTINCT if(MonthName(TRANDATE)=MonthName(AddMonths(Today(),-1)),CUSTOMER_ID))

Re: Formula for new transactions for new customers (never ordered before)

marcus_sommer — Tue, 13 Jan 2015 19:47:41 GMT

I think you could try it with InMonthToDate() and using a min(date) or Firstsortedvalue(date) as comparing-date to today and wrapped this in a count(aggr()).

- Marcus

Re: Formula for new transactions for new customers (never ordered before)

klaus_feldam — Tue, 13 Jan 2015 20:19:00 GMT

Thanks, Elena.

It looks like there is a formula error in what you are suggesting;

"Error in set modifier element function set"

The error is in the following part of the formula:

"{<TRANDATE=e({<(Month(TRANDATE)<Month(AddMonths(Today(),-1))>}TRANDATE)>}"

Re: Formula for new transactions for new customers (never ordered before)

klaus_feldam — Tue, 13 Jan 2015 20:30:32 GMT

Marcus,

That sounds like a reasonable idea.

Do you have any suggestions for the formula based on my original formula?

Something like this?

aggr( min ( <[CUSTOMER_ID]> [TRANDATE] < Addmonths(Today(),-1), [TRANDATE], CUSTOMER_ID))

I didn't test my quick stab at a formula above - just trying to align with your suggestion.

Re: Formula for new transactions for new customers (never ordered before)

Anonymous — Tue, 13 Jan 2015 20:37:44 GMT

Yes, sorry...

It's not correct the set analysis syntax

If you had field Month (=Month(TRANDATE)), you could use:

{<TRANDATE=e({<(Month={"<Month(AddMonths(Today(),-1))"}>} TRANDATE)>}

Re: Formula for new transactions for new customers (never ordered before)

marcus_sommer — Wed, 14 Jan 2015 06:08:57 GMT

Try this:

sum(aggr(-inmonthtodate(min({1} TRANDATE), today(), 0), CUSTOMER_ID))

expression logic:

check if the min-date within your target-range, if true = -1, the - before inmonth … returned 1, if false it returned 0

this will be executed for each customer and then aggregated

firstly - put the min() and the inmonthtodate() in separate expressions and check the results

- Marcus