Only include sales people with minimum number of calls

[Anonymous]

I have a tree map that shows sales people where the size of each square is determined by their sales amount.  However, I need to only show sales people that made a minimum number of sales phone calls.

E.g. if the threshold is 20, then the tree map should exclude any sales people that didn't make (at least) 20 phone calls.

How do I go about achieving this?

[rwunderlich · Accepted solution]

sum({<SalesPerson={"=count(CallId)>20"}>} Amount)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Thanks, that looks like the correct way to do it.  It works when I use straight fields; when I use the variables that are coming in through the Data Load Editor, it shows the text "The chart is not displayed because it contains only undefined values".

In your formula sum({<SalesPerson={"=count(CallId)>20"}>} Amount) ...

• count(CallId) = this comes in as a variable "vNoOfCalls" which is defined as "Sum ( If (Activity = 'Call', ActivityCnt))".  Updating your formula to "sum({<SalesPerson={"=$(vNoOfCalls)>20"}>} Amount)" seems to work.
• Amount = this is another calculation in the data load as variable "vSuccessRatePayments" = "$(vClearedTotalDollars) / $(vTotalPayments)", where for example the denominator is "Sum ( If (Activity <> 'Call', ActivityValue))"

So, when I change Amount to the variable "$(vSuccessRatePayments)", it breaks.

Sum({<Admin={"=Num($(vNoOfCalls))>20"}>} $(vSuccessRatePayments))

Perhaps the Sum is the issue since $(vSuccessRatePayments) is already a Sum?  I tried Aggr, but same result.

[rwunderlich]

The problem would be a sum(sum()) which requires an aggr. But I wouldn't go that route, it's more complicated than necessary.  Instead add your if() criteria into the set:

Sum({<Admin={"=Num($(vNoOfCalls))>20"}, Activity-={'Call'}>} ActivityValue)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com