Hi could you attach a "Packaged Workbook" of the same instead?
you need some calculated fields
1. Head Cound, this only counts on occasions when a manager appears
2. next to thi you need create 2 field by column (N° REP sum()=0 & %SUM()=0)
the N° REP count the occation when a REP appears in the group
% SUM will formulate the result
SampleData.twbx 18.4 KB
Thank you @Deepak and @Pablo for your quick responses. I tried to work with your soultions but it didn't work. I realized that my data has multiple records for same REP. So, it doesn't allow me to calculate bin on SUM(adjusted_Rev).
I have modified my sample data according to my requirement and attaching the updated data below.
Extremely sorry about my negligence before.
Director Manager REP Date Delivered Adjusted Rev Mich Aaron Conn August 45 Mich Aaron Conn August 47 Mich Aaron Ronn January 19 Mich Aaron Mich September 48 Mich Aaron Ryan April 13 Mich Aaron Trev April 47 Mich Aaron Trev April 10 Mich Aaron Ronn January 7 Mich Aaron Ryan February 23 Mich Aaron Ryan April 48 Mich Aaron Ronn February 27 Mich Aaron Trev February 36 Mich Aaron Trev January 18 Mich Aaron Trev March 18 Mich Aaron Gary April 26 Mich Aaron Trev April 25 Mich Aaron Trev April 13 Mich Aaron Gary February 20 Mich Aaron Trev MArch 32
For ex: I want to see in April Month , How my Manager Aaron's team is performing.
For April Month Manager HeadCount(No.of distinct Reps) %REPS with sum (REV)=0 %REPS with sum (REV) between $0-$50 %REPS with sum (REV)> 0 Aaron 3 0 33% 67%
SampleData.twb 19.9 KB
Are you looking for this ?
> 50. =
if SUM([Adjusted Rev]) > 50 then 1 else 0 end
if SUM([Adjusted Rev]) > 0 and SUM([Adjusted Rev]) <=50 then 1 else 0 end
if SUM([Adjusted Rev]) =0 then 1 else 0 end
window_sum( [< 50.]/WINDOW_COUNT(COUNTD([Adjusted Rev])))*100
window_sum( [> 50. (copy)]/WINDOW_COUNT(COUNTD([Adjusted Rev]))) * 100
window_sum( /WINDOW_COUNT(COUNTD([Adjusted Rev]))) * 100
If yes, PFA.
Sales Comparison v3.twbx 488.6 KB
Thank you for your reply. I am trying to do exactly the way you did it. But somehow my WIndow_SUM calculations are coming as measures . Your window_Sum calculations in Sheet 8 are coming as dimensions. How to do this??
Mine are also coming under measure only but turned those to discrete ?
I only made Headcount as Dimension by doing as below but probably that will not make any difference here
Also take care of Compute Using here as below ---->
Then I did formatting at the end (right click on number and then format )
Hope it helped. If yes please mark it as CORRECT and HELPFUL so that it can help others.
Thank you for your reply. I was getting desired result for one director. But if i select multiple directors then it wasnt calculating for each director rather it was just copying the results of one director to all other directors. But your post was really helpful in understanding the window_sum concept and ways to format the results according to the needs. Really appreciate your help ! Thanks!
why we have to use MIN function in COUNTD([REP]) / MIN([YF : Total REPs]) ?
It's because the actual aggregation -- again the COUNTD( [REP] ) --
is made inside the FIXED LOD expression, which is the [YF : Total REPs] itself.
The MIN() is the outer 'wrapper' aggregation of the inner FIXED LOD expression
(the Row-Level calculation). It is to formally make the Denominator an aggregate, too.
It could be ATTR() , MIN() , MAX() or AVG() as well -- but not the SUM(), though ;-)