Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to count total base (customers who are ordering from starting of the company) on month basis.
I have following fields.
Customer No
Order date
Order month
Order year
Order Monthyear
Order No
Example:
In 2018 Jan lets say company started trading and had 10 customers in the first month and in Feb another 20 customers purchased.
I am trying to calculate month end basis. At the end of Jan its 10 and At end of Feb it's 30 ,even though all 30 customers didn't order I still want to include them.
Month | Total New customers | Base customers |
---|---|---|
Jan | 10 | 10 |
Feb | 20 | 30 |
thanks for your time in advance.
Hi,
expression for base customers :
= if(rowno()=1,new, new + above([Base customers]))
regards
Hi Olivier,
I do not have New and base customers fields, I have customer No. only. I have given only example header.
Thank you
so, in your script :
to make the flag new you'll be able to count :
tmp:
load Customer No,minString(order date) as minDate resident Order group by Customer No;
join (Order) load Customer No,minDate resident tmp;
load *, if(OrderDate=minDate,1) as flagNew resident Order;
drop tmp;
How do you intend to calculate total new customer?