topic Re: Aggr or Exist function syntax help: New customers added this year in QlikView

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

Wed, 12 Oct 2011 20:02:06 GMT

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.

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

Thu, 13 Oct 2011 10:12:01 GMT

Hi, I modified expression.Once check the attached file

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

Thu, 13 Oct 2011 10:13:38 GMT

Use the following Expression:

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

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

Thu, 13 Oct 2011 15:01:35 GMT

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.

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

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 17:43:19 GMT

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

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

Thu, 13 Oct 2011 19:28:54 GMT

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

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

Thu, 13 Oct 2011 20:04:07 GMT

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.

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

Thu, 13 Oct 2011 20:21:14 GMT

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"

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

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

Thu, 13 Oct 2011 22:39:32 GMT

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

Hans

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

Thu, 13 Oct 2011 23:23:54 GMT

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.

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

swuehl — Fri, 14 Oct 2011 01:01:49 GMT

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

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

Fri, 14 Oct 2011 02:04:06 GMT

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

swuehl

thanks for the input.

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

sna — Fri, 14 Oct 2011 02:10:40 GMT

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')

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

Fri, 14 Oct 2011 05:04:30 GMT

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')

Hans

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

Fri, 14 Oct 2011 16:03:15 GMT

Shima

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

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

Fri, 14 Oct 2011 16:05:25 GMT

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?

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

Sat, 15 Oct 2011 09:03:09 GMT

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...

Hans

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

Sat, 15 Oct 2011 14:01:02 GMT

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...

Hans