13 Replies Latest reply: Feb 21, 2017 2:35 PM by Maria Harmening

# set analysis

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

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!!!

• ###### Re: set analysis

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

• ###### Re: set analysis

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..

• ###### Re: set analysis

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:

• ###### Re: set analysis

what is Dual('')?

• ###### Re: set analysis

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

• ###### Re: set analysis

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)

))))))

• ###### Re: set analysis

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)))))))

• ###### Re: set analysis

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.

• ###### Re: set analysis

I think it should work, check this out:

Buckets

• ###### Re: set analysis

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

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?

jog                          4

swim                       2

relax                       1

clean                       1

sleep                       1

jumpingjax               1

• ###### Re: set analysis

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?

• ###### Re: set analysis

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]))