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

    set analysis

    Maria Harmening

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

        • Re: set analysis
          Maria Harmening

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

            • Re: set analysis
              Maria Harmening

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

                    • Re: set analysis
                      Maria Harmening

                      what is Dual('')?

                        • Re: set analysis
                          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

                            • Re: set analysis
                              Maria Harmening

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

                                    • Re: set analysis
                                      Maria Harmening

                                      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
                                          Sunny Talwar

                                          I think it should work, check this out:

                                          Buckets

                                            • Re: set analysis
                                              Maria Harmening
                                                • Re: set analysis
                                                  Maria Harmening

                                                  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

                                                    • Re: set analysis
                                                      Maria Harmening

                                                      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
                                                          Maria Harmening

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