en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Martin O'Loughlin  
#1 Posted : Friday, November 30, 2018 2:49:54 PM(UTC)
Martin O'Loughlin
Rank: Member

Groups: extranet\Forum
Joined: 5/14/2018(UTC)
Posts: 5

I have a requirement to count the number of team members who have sold above a threshold and present only the number.

the presentation would be team vertically, then two numbers, first total number of team members, secondly all team members who reached the threshold.

I can use count(all,0,0) and then use visibilty to hide the team members and present a number that represents all members.

Is there something like count(all(>500),0,0) to bring back the second number.


If it matters i am currently using Targit 2017.
Ole  
#2 Posted : Sunday, December 2, 2018 9:04:00 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 448

Hi Martin,

I presume you have a crosstab, generally looking at one measure (revenue), teams vertically and team members horizontally.

You could then do it by adding three calculations:

1. Add "as a new measure": if sum(0, 0, m1) > 500 then 1 else 0
2. Add as a new column: count(all, 0, m1) ... this would be total number of team members.
3. Add as a new column: sum(all, 0, m2) ... this would be team members above the threshold.

Finally, go to the Properties tab, Visibility, to hide your two measures (the original one and your calculated one). That should end up with your desired two numbers for each team.

BR / Ole
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Martin O'Loughlin  
#3 Posted : Tuesday, December 4, 2018 8:51:59 AM(UTC)
Martin O'Loughlin
Rank: Member

Groups: extranet\Forum
Joined: 5/14/2018(UTC)
Posts: 5

yes that works to a point, in the actual version there is one complication which is causing me an issue and i hadn't comprehended the issue when i posted originally.

Yes it is a crosstab,

Vertically i have teams, horizontally i have team members.

I have one measure turnover, but the complication is as follows.

I have a comparison on the horizontal, that is designed to return as the first number turnover of one group of products, and the second element is turnover on a different group of products.


What i have then created is two new measures, both of these measures are based on if value>threshold then 1 else 0 this then tells me if a team member has exceeded the target for group a and also the target for group b.

The point of this is to identify team members who have sold the first group and who have not sold products in the second complimentary group. the indicator being percentage of team selling products to the expected level in both categories

i have then added two count columns etc

The issue i have is that the sum(all) includes both group a and group b from the comparison.

What i need to achieve is all team members who have sold group a above the threshold and all team members who have sold group b above that threshold.

So what i need is sum(all(comparison group a)) and sum(all(comparison group b))

Ole  
#4 Posted : Tuesday, December 4, 2018 9:27:26 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 448

Any chance you can upload one or more screenshots to help understanding your setup and the issue?

In case you need it: https://www.targit.com/e...?g=posts&t=66#post91

BR / Ole
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Martin O'Loughlin  
#5 Posted : Tuesday, December 4, 2018 12:12:58 PM(UTC)
Martin O'Loughlin
Rank: Member

Groups: extranet\Forum
Joined: 5/14/2018(UTC)
Posts: 5

UserPostedImage

hopefully this will make sense,

I have broken the image into its parts, and colourcoded which measure depends on which.

I have put a filter on the result to only have one sales team, the first group is group a products, and note weight says 0 this is because the weight is too low to count,

note in the second group the margin says 1 because it is high enough.

What i need in the third group is weight to be 0 and margin to be 1. if i were to expand the whole the third group 1 should be a sum of all first group products and margin should be a sum of all second group margins.

Edited by user Tuesday, December 4, 2018 12:18:13 PM(UTC)  | Reason: Not specified

Ole  
#6 Posted : Tuesday, December 4, 2018 12:45:44 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 448

You mention that you created two new measures. If you want the first measure to work only on the first group (and always be zero in the second group) you can do it something like this:

if allcount(d1:0(l(1,0)), d1, m1) = 1 and sum(0, 0, m1) > 500 then 1 else 0


And similar for the second calculated measure:

if allcount(d1:0(l(1,0)), d1, m1) = 2 and sum(0, 0, m1) > 500 then 1 else 0

Let me know if this will bring you closer to your goal.
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Martin O'Loughlin  
#7 Posted : Tuesday, December 4, 2018 3:10:04 PM(UTC)
Martin O'Loughlin
Rank: Member

Groups: extranet\Forum
Joined: 5/14/2018(UTC)
Posts: 5

It is almost there, i now have the correct number of agents who have crossed the group a threshold. and i also have the number who have crossed the group b threshold.

However there is one hopefully minor issue remaining, the correct answer to group b is those who have crossed the group a threshold and the group b threshold.

ie i have in one sales team 14 who have crossed the group a threshold, and 15 who have crossed the group b threshold, but the answer is who of the 14 have crossed the group b threshold, which should be 9.

I need this basic code tweaked to included a reference to the other group,

if allcount(d1:0(l(1,0)), d1, m1) = 1 and sum(0, 0, m1) > 500 and (REFERENCE TO OTHER SIDE OF COMPARISON) then 1 else 0

What i need to be able to do is query the value of a measure in the other half of the comparison to see if it has exceeded a threshold?
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error

We use cookies to improve your site experience, but they also provide us with information on your use of our website.
To find out more about the cookies we use and how to delete them, see our Privacy Policy. By continuing to browse the site, you are consenting to our use of cookies.

ok