Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get a calculated field that will flag "New" customers and "Existing Customers" based on the Customer Shipto Number not sales
Critera:
New Customer - Shipto Customer number "Only Exists in Current Year" and not in any prior years
Existing Customer- Shipto Customer Number exists in any of the previous Fiscal Years and may exist in current fiscal year but not have any sales
my latest attempt is below which did not work:
=if(Aggr($(sCYear)[Fiscal Year],[ShipTo Customer])=Aggr($(sPYear)[Fiscal Year],[ShipTo Customer]) OR Aggr($(sCYear)[Fiscal Year],[ShipTo Customer])=Aggr($(sP2Year)[Fiscal Year],[ShipTo Customer]),'Existing','New')
I prefer to use [ShipTo Customer number] instead of Sales to evaluate because it is possible for a customer to exist but not have any sales in the current year.
For example we just finished our fiscal year in September 2011, October 2011 begins the current fiscal year,
if a customer has sales in October2011 but not in October 2010 that doesnt mean this customer is new, if this customer already exsited but just didnt have sales in the current year.
Please advise if Aggr is not the best approach
$(sCYear)
is a variable created based on the below:
{$<[Fiscal Year]={$(#vCurrYr)},[Fiscal Quarter]=,[Fiscal Month Name]= >}
$(sPYear)
is a variable created based on below:
{$<[Fiscal Year]={$(#vPrevYr)},[Fiscal Quarter]=,[Fiscal Month Name]= >}
$(sP2Year)
is PrevYR-1
attached is a QVW for 2011 & 2010 Sales by customer number.
Hi, I modified expression.Once check the attached file
Use the following Expression:
=if(Aggr(max([Fiscal Year]),[ShipTo Customer])=Aggr(Min([Fiscal Year]),[ShipTo Customer]),'New','Existed')
Thank you niranjan for your post, however
If the ShipTo Customer is new in Fiscal Year 2011, then that customer should not have any sales in 2010,
Only existing customers will have sales in both year 2011 and 2010
the above and attached QVW is showing New customers with both 2011 and 2010 sales and Existing customers with only 2010 sales.
Not sure how to correct.
"New" should show only sales in the current year becasue the customer would not have existed in the previous year, however "Existing" would have sales in both years.
Hi,
If I got it right, why not using the expression's labels that already exist
If([Sales$(vPrevYr)] = 0, 'New', 'Existing')
It should show "New" whenever the user has zero sales in the previous year.
Is that what you are looking for?
BI Consultant
Hello Joseph,
two things to mention:
In your expression is a colon missing (I put it in __) -->
=if(Aggr(max([Fiscal Year]),[ShipTo Customer])=Aggr($(sPYear) , [Fiscal Year],[ShipTo Customer]),'New','Existing')
Im not sure if the expression above (with correct syntax)is working fine, I suggest another version using [Sales Amont] of last year > 0 which should point into right direction:
=if( aggr(sum({<[Fiscal Year]= {'$(vPrevYr)'} >} [Sales Amount]), [ShipTo Customer]) > 0,'New','Existing')
HtH
Roland
Roland,
thank you for your input. I added your recommended changes, please see attached QVW.
Our fiscal Year runs from Oct-Sep
I added in the fiscal month-year dimension to better show results
The first expression (based on customer number)
Showed a New customer as existing, which i cant figure out why.
=if(Aggr(max([Fiscal Year]),[ShipTo Customer])=Aggr($(sPYear) , [Fiscal Year],[ShipTo Customer]),'New','Existing')
The second expression (based on sales)
showed the same result, a New customer showed as Existing
=if( aggr(sum({<[Fiscal Year]= {'$(vPrevYr)'} >} [Sales Amount]), [ShipTo Customer]) > 0,'New','Existing')
IF there is a way to check, if customer number existed in in Previous Year, Previous Year- 1 and Current Year, than that customer is "Existing"
If Customer number only exists in Current Fiscal Year than it is a New Customer.
below is my latest Expression in an attempt to identify 'New"
If Shipto Customer numbers that only exist in the Current Year as 'New"
If existed in previous year, previous year -1 "and" current year, than flag as "Existing"
=if(Aggr($(sCYear)[Fiscal Year],[ShipTo Customer])=Aggr($(sPYear)[Fiscal Year],[ShipTo Customer]) OR Aggr($(sCYear)[Fiscal Year],[ShipTo Customer])=Aggr($(sP2Year)[Fiscal Year],[ShipTo Customer]),'Existing','New')
The above is still showing sales in prior years as a 'New" customer which is incorrect
$(sCYear) = current fiscal year
$(sPYear)= prior fiscal year
$(sP2Year= prior fiscal year-1
very much appreciated for your input!!!
Cust No 1101200 & 12796750 are calculated correclty based on your attached QVW.
For the app that i am working on, the Calc dimension was my first option however upon reveiw, not all of the
calc dimension results are equal to the expression results and it appears that the expression results seem to be more consistant as a result i will used the expression.
I was not aware that non numeric or % could be popultated in the expressions column as you have done attached.
I will look into my variable, data and rerun.
This will be a big help and help me to move forward on this project.