topic Re: Counting with conditional expression in QlikView

Counting with conditional expression

Thu, 31 Mar 2016 17:26:37 GMT

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,

Re: Counting with conditional expression

sunny_talwar — Thu, 31 Mar 2016 17:40:53 GMT

May be this:

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

Re: Counting with conditional expression

swuehl — Thu, 31 Mar 2016 17:40:55 GMT

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.

Re: Counting with conditional expression

Thu, 31 Mar 2016 17:58:41 GMT

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,

Re: Counting with conditional expression

swuehl — Thu, 31 Mar 2016 20:58:18 GMT

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?

Re: Counting with conditional expression

Thu, 31 Mar 2016 22:31:34 GMT

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,

Re: Counting with conditional expression

swuehl — Fri, 01 Apr 2016 11:52:29 GMT

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)

)

Re: Counting with conditional expression

Fri, 01 Apr 2016 15:05:52 GMT

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,

Re: Counting with conditional expression

swuehl — Fri, 01 Apr 2016 15:42:51 GMT

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

)

Re: Counting with conditional expression

Fri, 01 Apr 2016 18:49:19 GMT

thanks a lot, this worked great