Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Counting with conditional expression

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,

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Counting with conditional expression

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

)

View solution in original post

9 Replies
Highlighted

Re: Counting with conditional expression

May be this:

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

Highlighted
MVP
MVP

Re: Counting with conditional expression

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.

Highlighted
Not applicable

Re: Counting with conditional expression

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

Highlighted
MVP
MVP

Re: Counting with conditional expression

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?

Highlighted
Not applicable

Re: Counting with conditional expression

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

Highlighted
MVP
MVP

Re: Counting with conditional expression

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)

)

Highlighted
Not applicable

Re: Counting with conditional expression

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

Highlighted
MVP
MVP

Re: Counting with conditional expression

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

)

View solution in original post

Highlighted
Not applicable

Re: Counting with conditional expression

thanks a lot, this worked great