2 Replies Latest reply: Dec 20, 2017 3:21 AM by Jaroslav Stanek RSS

    Calculated dimension

    Jaroslav Stanek

      Hello,

       

      Im dealing with current problem, hopefully someone will have some idea I got input data like this:

         

      MonthUserNumber of sales
      IXA12
      XA15
      XIA19
      XIIA11
      XIB7
      XIIB14
      XIIC10

       

      The usual table output would be:

          

      IXXXIXII
      A12151911
      B 714
      C 10

       

      But I would need:

          

      1234
      A12151911
      B714
      C10

       

      Basicly I need to compare employees based on number of months they are with company. I think calculated dimension is way to go as far as the input table is not the simple as the above one :-) Its made from more tables & their connections... Any hints?

        • Re: Calculated dimension
          Felip Drechsler

          Hi Jaroslav,

           

          You'll have to do some script transformations to get what you need, by getting the minimum month for each user (I also changed the roman literal month number into a integer number):

           

          dummy:

          Load * Inline

          [

          Month,User,Number of sales

          9,A,12

          10,A,15

          11,A,19

          12,A,11

          11,B,7

          12,B,14

          12,C,10

          ];

           

          Max:

          load

          User,

          min(Month) as minMonth

          Resident dummy

          group by User;

           

          left join (dummy)

          Load

          User,

          minMonth as Month,

          1 as [Num]

          Resident Max;

           

          NoConcatenate

          data:

          Load

          User,

          Month,

          [Number of sales],

          if (User = peek(User), peek(Num)+1,1) as Num;

          Load

          *

          Resident dummy

          order by User;

           

          drop table dummy, Max;

           

          The following code gets me both of your tables above:

          Sample.png

           

          Attached the QVW I used.