Counting with conditional expression

[#1]

Hello,

I was wondering if anyone can help me create an expression im having trouble with.

First of all, what i need is an expression that would count the distinct names of salespersons that have in a day a total of sales higher than X.

The thing is that my expression to get the totals of sales is:   Aggr(Price_Total*ExchangeRate,OrderItem_ID). This expression is on a table with dates as dimension. This table gives information of the total sales for each salesperson per day.

What i need is another table that would count how many salesperson have a total sale above X number.

Any ideas?

thanks,

[sunny_talwar]

May be this:

Count(DISTINCT If(Aggr(Price_Total*ExchangeRate, OrderItem_ID) > X, SalesPerson))

[swuehl]

Maybe something like

=Sum( Aggr( If( Sum(Aggr(Only(Price_Total*ExchangeRate), Dates,SalesPersion,OrderItem_ID)) > 1000, 1,0), Dates, SalesPerson))

// replace 1000 with your X

In a chart with dimension Dates.

edit: This could probably made much more simple, start with calculating your sales in the script.

[#4]

This got my problem 90% solved. The thing that is not working are the total expressions. This is adding the numbers of the previuos days. What i would like it to happen at the totals is that it would display the number of SalesPerson that on that Month it would count how many SalesPerson are above X number.

The difference is that now if someone is above that X on 5 days, it would count him as 5 not as 1.

thanks for the help,

KR

[swuehl]

Maybe something like this (assuming it is sufficient to pass the threshold once for any given sales person)

If(Dimensionality()=0, // Use SecondaryDimensionality() for a pivoted dimension total

Sum( Aggr( If( Sum(Aggr(Only(Price_Total*ExchangeRate), Dates,SalesPersion,OrderItem_ID)) > 1000, 1), SalesPerson)),

Sum( Aggr( If( Sum(Aggr(Only(Price_Total*ExchangeRate), Dates,SalesPersion,OrderItem_ID)) > 1000, 1), Dates, SalesPerson))

)

but, these kind of request are easier to resolve with some sample data to validate against, don't you think so?

[#6]

Yes it would,

I was trying to do something similar to what you suggested but it wont work.

I created a small file with some example data so i could share it.

Both tables of the file are the ones im creating.

The first one is just the ammount for each salesman for each day. The second table has some information about the sales for each day, and the red line is the one im having trouble in the Total part.

thanks for the support,

KR

[swuehl]

I am not really sure how you want to aggregate your "above quota" to week or total level.

Do you want to count each SalesPerson that has exceeded the quota at least once?

Then maybe just

=Count(DISTINCT 

     Aggr( If( Sum(Aggr((Price_TE), Created_New,SalesPersonName,OrderItem_ID)) >53000, SalesPersonName), Created_New, WeekC,SalesPersonName)

)

[#8]

Not really, what i want is that on the total part of the table it would do the comparison to the total amount of that week and count the SalesPerson that are above X.

This X would be another value than the daily one, but that issue is already solved, i just want the formula to add the Price_TE for that number of days and compare that total once for each salesman, so i can know wich ones are above X.

Am I making myself clear?

thanks for the help,

KR

[swuehl · Accepted solution]

Maybe like

=If(SecondaryDimensionality() >0, // need to adapt when adding more dimensions

Sum( Aggr( If( Sum(Aggr((Price_TE), Created_New,SalesPersonName,OrderItem_ID)) >53000, 1,0), Created_New, WeekC,SalesPersonName)),

Sum( Aggr( If( Sum(Aggr((Price_TE), WeekC,SalesPersonName,OrderItem_ID)) >53000*4, 1,0),  WeekC,SalesPersonName))  //53000*4 just for testing

)

[#10]

thanks a lot, this worked great