Aggr or Exist function syntax help: New customers added this year

[#1]

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.

[swuehl]

Joseph,

it seems to me that you have also some duplicate records.

For example, check / count the records for [ShipTo Customer] = 10022000 at Date 03-29-2011.

Then I noticed that there are some "balanced" records, 2 records with identical abs. Sales Amount but +/- sign, thus their sum is zero. But maybe this is ok, i don't know your business.

Regards,

Stefan

[#3]

Once i added to the live App, and used customer number instead of shipto my Calc dimension and Calc expression both tied and data irregualarities were fixed

JB

swuehl

thanks for the input.

[sna]

Here is another one using indirect set analysis. I included all years (2008, 2009 and 2010) to identify the new customer.  if you want to look back only a couple of years, you can remove $(=vPrevYr2-1).  Does this work for you?

=

if(Aggr(Count(distinct {<[ShipTo Customer] = E({<[Fiscal Year] = {$(vPrevYr) , $(vPrevYr2), $(=vPrevYr2-1)}, "Sum([Fiscal Year] = {$(vPrevYr), $(vPrevYr2), $(=vPrevYr2-1)} [Sales Amount])=0">}) >}[ShipTo Customer]), [ShipTo Customer]) = 1, 'YES', 'NO')

[#5]

If you want to look at all prev year,

You could use "<$(Max([Fiscal Year])" instead of your list of variables.

Joseph, you can also use

if(Sum({1-1<[Fiscal Year]={$(=Max([Fiscal Year]))}>[Sales Amount]*-1)<>0,'N','E')

BR

Hans

[#6]

Shima

your recommendation also worked, Thank your for your help and input.

[#7]

hansiola

i tried

if(Sum({1-1<[Fiscal Year]={$(=Max([Fiscal Year]))}>[Sales Amount]*-1)<>0,'N','E')

couldnt get this one to work, could you add to my QVW in this thread?

JB

[#8]

Hi,

Sorry, I did this without my QV... So I missed a char...
The correct expression shall be:

if(Sum({1-1<[Fiscal Year]={$(=Max([Fiscal Year]))}>}([Sales Amount]*-1))=0,'New','Existing')

It differs a little bit from the previous expressions.
They treated customer with no sales in any year as existing, due to the first condition had a operator <>0

This will treat these customers as New. If you remove the first condition you vill end uppwith teh same result...

I.e.

if(sum({$<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>}[Sales Amount]*-1)=0,'New','Existing')

Why wolud we use the first expression? No reason, just wanted you to observe that you could do it like that...

I another expression it would be good to use a SET operator...

The SET Analysis syntax look complicated but it isn't. I fairly easy and logical. Take a look inside the help-file.
I contains a lot of information and is pretty straight forward.

So if you want to take out the effekt of selecting a Fiscal Month Name you simply have to give that field a selection in the SET expression, a selection of nothing...

if( sum({$<[Fiscal Year]={"<$(=max([Fiscal Year]))"}, [Fiscal Month Name]= >}[Sales Amount]*-1)=0,'New','Existing')

You have to include every field that the user might do a select in, for witch you want to cancel the effekt...

BR

Hans

[#9]

Hi Joseph,

I got odd behavior when using the formula as dimension. But added a control for null and it works much better.

I also missed a >= in the 1-1<>  expresson, should be as folowing to work when selecting a lower fiscal year:

if(Sum({1-1<[Fiscal Year]={">=$(=Max([Fiscal Year]))"},[Fiscal Month Name]=,[ShipTo Customer]=>}(fabs([Sales Amount])*-1))=0,'New','Existing')

I also added a fabs() in the expressions. Due to in some year you had for example 79 and -79 witch resulted in a 0 for that year.

Added the QVW...

BR

Hans