set analysis

[Anonymous]

I have a measure that looks like this.

Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Task Name], [Emp Name])), [Task Name])

it allows me to group by task name, then by employee and it sums the number of employees that have hours within that task.

so that when I chart, I know how many people are within each task.  so, on a table it would look like this

Task Name            Employees per Task

Dancing                         5

Jogging                          6

Swimming                      2

Skating                           1

I want to create a histogram that would count the tasks that have between 0-3 people, count the tasks that have between 4-7, etc.

can I use the above set in the count function?  can I have a count on Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Task Name], [Emp Name])), [Task Name]) ?  I don't know how to do this?  and I need to have it done by tomorrow!!!

[Anonymous]

I have an excel table that I'm loading that looks like this

planned_hours             task_name                   emp_name

0                                   jog                                Joe

5                                   jog                                Jen

10                                 jog                                Jerry

15                                 jog                                Jan

20                                 jog                                Snoopy

25                                 swim                             Charlie

30                                 swim                             Lola

35                                 relax                             Maria

40                                 clean                             Sunny

45                                 sleep                            Maria

50                                 jumpingjax                   Molly

if I use this Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Task Name], [Emp Name])), [Task Name]), does this give me an array of values like this?

task name              emp per task

jog                          4

swim                       2

relax                       1

clean                       1

sleep                       1

jumpingjax               1

[Anonymous]

wouldn't I then have to use the  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Task Name], [Emp Name])), [Task Name]) to calculate the count of macros of in those bins?

[Anonymous · Accepted solution]

yeah!!!!  it worked.

I used this as the dimension/BINS for the histogram.

If(

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) >= 0 and

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) < 4, Dual('0-3', 3),

If(

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) < 8, Dual('4-7', 7),

If(

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) < 12, Dual('8-11', 11),

If(

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) < 16, Dual('12-15', 15),

If(

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) < 20, Dual('16-19', 19),

If(

  Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]) < 24, Dual('20-23', 23), Dual('24-100', 100)))))))

and for the measure I used

count(Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Micro], [Pva Emp Name])), [Pva Micro]))