Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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
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)>}"
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.
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)>}
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