Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
klaus_feldam
Creator II
Creator II

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

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.

6 Replies
elena888
Specialist
Specialist

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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

klaus_feldam
Creator II
Creator II
Author

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)>}"

klaus_feldam
Creator II
Creator II
Author

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.

elena888
Specialist
Specialist

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)>}

marcus_sommer
MVP & Luminary
MVP & Luminary

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