Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
Not applicable
Author

Hi Joseph,
I looked at your app...
I think you have a problem with your variables.
With your formulas I don't get the same Sales Amount Value for Fiscal Year 2010 when I use it as a Prev Year value compared to a Curr Year value.
Same for 2009. Look inside the attached file...  I put som textboxes there...
But anyway I whoulden't bother to make variables. Why use Calender date? Why not Fiscal Year in the Set-expression.
Take a look at my Sales columns...
Then, the New and Existing customer...
I added a few columns in your chart just to be able too look att the data...
The two columns that are of interesst is column 2 and 10
Is it important that you have the New/Existing as a Dimension? It will be a calculated dimension and be bad for performance.It too complicate things.
If you can use the New/Existing as a expression column you will be better off and get better formulas...
So if you have to use it as a Dimension, add following as calculated Dimension:
=
if(aggr(sum({$<[Fiscal Year]={$(=max([Fiscal Year])) }>}[Sales Amount]*-1)<>0 and sum({$<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>}[Sales Amount]*-1)=0,[ShipTo Customer]),'New','Existing')
If you can use it as a Expression, add following as an expression :
=
if(sum({$<[Fiscal Year]={$(=max([Fiscal Year])) }>}[Sales Amount]*-1)<>0 and sum({$<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>}[Sales Amount]*-1)=0,'New','Existing')
So consider where to put the logic and revisit your variable ePYYTDSale
BR
Hans

View solution in original post

17 Replies
Not applicable
Author

Hi, I modified expression.Once check the attached file

Not applicable
Author

Use the following Expression:

=if(Aggr(max([Fiscal Year]),[ShipTo Customer])=Aggr(Min([Fiscal Year]),[ShipTo Customer]),'New','Existed')

Not applicable
Author

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.

Miguel_Angel_Baeyens

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?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Hi Joseph,
I looked at your app...
I think you have a problem with your variables.
With your formulas I don't get the same Sales Amount Value for Fiscal Year 2010 when I use it as a Prev Year value compared to a Curr Year value.
Same for 2009. Look inside the attached file...  I put som textboxes there...
But anyway I whoulden't bother to make variables. Why use Calender date? Why not Fiscal Year in the Set-expression.
Take a look at my Sales columns...
Then, the New and Existing customer...
I added a few columns in your chart just to be able too look att the data...
The two columns that are of interesst is column 2 and 10
Is it important that you have the New/Existing as a Dimension? It will be a calculated dimension and be bad for performance.It too complicate things.
If you can use the New/Existing as a expression column you will be better off and get better formulas...
So if you have to use it as a Dimension, add following as calculated Dimension:
=
if(aggr(sum({$<[Fiscal Year]={$(=max([Fiscal Year])) }>}[Sales Amount]*-1)<>0 and sum({$<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>}[Sales Amount]*-1)=0,[ShipTo Customer]),'New','Existing')
If you can use it as a Expression, add following as an expression :
=
if(sum({$<[Fiscal Year]={$(=max([Fiscal Year])) }>}[Sales Amount]*-1)<>0 and sum({$<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>}[Sales Amount]*-1)=0,'New','Existing')
So consider where to put the logic and revisit your variable ePYYTDSale
BR
Hans
Not applicable
Author

hansiola,

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.