7 Replies Latest reply: Aug 22, 2012 4:05 AM by David Ambrozie RSS

    Calulated dimensions or ...

    Frank Beunder

      Hello,

       

      I have the following  question:

       

      I have a couple of values a day. For every
      day I want to calculate an average. Each day could have a score between 0 and
      10. So far no problem.

       

      Now I want to create (not yet existing
      in the data) a dimension like this:

       

      DayScore

      0 < 3
      3 < 6
      6 < 9
      9 or 10

       

       

      Now I want to count how many day’s there are in every DayScore

       

      How do I do this, while the dimensions does not exist in the dataset (please see attachment for an example set)

       

        • Re: Calulated dimensions or ...

          add a macro:

           

          Tools -> Edit module:

          function checkdays(value)

          if value<3 then

                              checkdays="0<3"

          elseif value<6 then

                              checkdays= "3<6"

          elseif value<9 then

                              checkdays= "6<9"

          else

                    checkdays ="9 or 10"

          end if

          end function

           

          then change your load script:

           

          Data:

          LOAD * INLINE [

              ID, DAY, VALUE

              1, 1, 2

              2, 1, 5

              3, 1, 8

              4, 1, 9

              5, 1, 9

              6, 2, 6

              7, 2, 8

              8, 2, 7

              9, 2, 6

              10, 2, 9

              11, 3, 5

              12, 3, 8

              13, 3, 9

              14, 3, 9

              15, 3, 6

              16, 4, 4

              17, 4, 5

              18, 4, 6

              19, 4, 4

              20, 4, 6

          ];

           

           

          Avg:

          Left Join(Data)

          Load DAY,

                    checkdays(avg(VALUE)) as DayAvg

          resident Data group by DAY;

           

           

          now you create a chart with DayAvg as dimension and count(distinct DAY) as expression

            • Re: Calulated dimensions or ...
              Frank Beunder

              Simon,

               

              Your solution is helpful, but of course my example is a simplification of the truth. I made it simple by having just one dimension 'DAY'  But in fact, the table has more dimensions. Day, Type, Color, ect.. The graph needs to recalculate the values for every selection I make. With your solution the result is hard coded in the script.

               

              Frank

            • Re: Calulated dimensions or ...
              David Ambrozie

              Hi Frank,

                   You can also use a calculated dimension and then count distinct day.

              See the example attached.

               

              Regards,

              David

                • Re: Calulated dimensions or ...
                  Frank Beunder

                  David,

                   

                  I think the calulated dimension is the way to go. My problem is, that it is a hughe dataset. Calculated dimensions always use a lot of CPU time.

                   

                  There is one more thing, 0<3 and ' 9 and 10'  are not shown, because of the zero value. Do you know how I can show them, even when the value is 0?

                   

                  frank

                    • Re: Calulated dimensions or ...
                      David Ambrozie

                      That is true, the if function will slow CPU a lot.

                       

                      You can try to calculate the avg within the script using group by, then make an intervalmatch by result.

                      It should be faster then 'if' function but still it won't display the zero value.

                       

                      Also, watch out for duplicate records when using intervalmatch, if avg(DAY)= 3 it is possible to link with 0<3 and also 3<6. To avoid that you should set min/max like: 0, 3 then 3.00001, 6. I haven't done that because of Thousand/Decimal separator.

                       

                       

                      value:

                      LOAD * INLINE [

                          ID, DAY, VALUE

                          1, 1, 2

                          2, 1, 5

                          3, 1, 8

                          4, 1, 9

                          5, 1, 9

                          6, 2, 6

                          7, 2, 8

                          8, 2, 7

                          9, 2, 6

                          10, 2, 9

                          11, 3, 5

                          12, 3, 8

                          13, 3, 9

                          14, 3, 9

                          15, 3, 6

                          16, 4, 4

                          17, 4, 5

                          18, 4, 6

                          19, 4, 4

                          20, 4, 6

                      ];

                       

                      score:

                      load * Inline [

                      min, max, scorename

                      0, 3, '0<3'

                      3, 6, '3<6'

                      6, 9, '6<9'

                      9, 10, '9 or 10'

                      ];

                       

                      values2:

                          load

                              avg(VALUE) as SCORE, DAY Resident value Group by DAY;

                       

                       

                      left join (values2) IntervalMatch (SCORE) load min,max Resident score;

                       

                      left join (values2) load * resident score;

                       

                      drop Table score;

                       

                      Good luck!