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]

so, it would be count the number of employees within each task that have planned hours >0,

[Anonymous]

and then count the number of tasks that have between 0 & 4 people and then count the number of task that have between the next interval..

[sunny_talwar]

May be this

Dimension:

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

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

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

Expression:

Count(DISTINCT [Task Name])

[Anonymous]

what is Dual('')?

[sunny_talwar]

Added that for sorting purposes. You can remove, if you don't need 0-3, 4-7, 8-10, 11-14 to be sorted in the ascending order

[Anonymous]

I have this, but it says there's an error in my expression.  what am I doing wrong?  and your saying that this should be the dimension for my 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', 1)),

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

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

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

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

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

Dual('24-100', 7)

))))))

[sunny_talwar]

Try this:

=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', 1),

If(

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

If(

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

If(

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

If(

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

If(

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

[Anonymous]

it's not working and I think it's because I'm trying to do this within a bar chart and not in a load script??.  I think the dimension is correct, but the measure of Count(DISTINCT [Task Name]) isn't working. 

wouldn't i need to map it with the dimensions?  how does it know where to put the counts within the histogram? 

should i tackle this differently all together?  should I modify the load script to tackle this?  i've been working on this for over a week and am completely frustrated.

[sunny_talwar]

I think it should work, check this out:

Buckets