7 Replies Latest reply: May 28, 2016 11:57 PM by Rohit Kumar RSS

    Create a calculated dimension by measure

    Rohit Kumar

      Hi,

      I have a requirement where i have three columns

       

      Customer  Order_No  Amount

          A            Order1    100

          A            Order2    400

         B            Order3    500

         C            Order4    400

          C            Order5    200


      Now I want to make a dimension where  I would have three values , Average Order value (Amount/count(Order_No)) <200,

      Average Order value (Amount/count(Order_No)) >200<500 and Average Order value (Amount/count(Order_No)) >500.

      So there shall be a new dimension which has there three values and I can use it as a proper dimension in tree chart ?

        • Re: Create a calculated dimension by measure
          Stefan Wühl

          I guess your internal grouping entity should be Customer?

           

          In the script:

           

          INPUT:

          LOAD * INLINE [

          Customer,  Order_No,  Amount

              A,            Order1,    100

              A,            Order2,    400

             B ,           Order3 ,   500

             C ,           Order4 ,   400

              C,            Order5,    200

          ];

           

           

          LOAD Customer, AvgOrderAmount,

                    If(AvgOrderAmount <200, Dual('<200',200),

                         If(AvgOrderAmount <500, Dual('>=200 <500',500),Dual('>=500',520))) as Group;

          LOAD Customer, Sum(Amount) / Count(Order_No) as AvgOrderAmount

          RESIDENT INPUT

          Group By Customer;

           

           

          You can also use

          =  If(Sum(Amount) / Count(Order_No) <200, Dual('<200',200),

                         If(Sum(Amount) / Count(Order_No) <500, Dual('>=200 <500',500),Dual('>=500',520)))

           

           

          as calculated dimension without the additional data model change

            • Re: Create a calculated dimension by measure
              Rohit Kumar

              Hi ,

              Thanks for the reply , I think I couldn't tell you the exact problem, there is a field Gross Amount , and NO_() which is the order number and i need to create a dimension which has 5 values , <1500, 1500< and 3500> , 3500< and less  than 7500, 7500< and <15000 and 15000<. I have tried your above formula but it is giving me an invalid dimension , Can you please help in this , this is on urgent basis requirement

              for two chunks I have tried below one

               

              If(Sum(Gross_Amount) / Count(No_) <1500, Dual('<1500',1500),

                             If(Sum(Gross_Amount) / Count(No_) <3500, Dual('>=1500 <3500',3500),Dual('>=3500',3520)))

                • Re: Create a calculated dimension by measure
                  Sunny Talwar

                  Try this using the aggr function:

                   

                  Aggr(If(Sum(Gross_Amount) / Count(No_) <1500, Dual('<1500',1500),

                                 If(Sum(Gross_Amount) / Count(No_) <3500, Dual('>=1500 <3500',3500),Dual('>=3500',3520))), NO_)

                  • Re: Create a calculated dimension by measure
                    Stefan Wühl

                    It would be easier to answer if you could post some sample records and your expected result.

                     

                    If your data looks like

                     

                    Gross Amount, NO_()

                    100, 1

                    1600,2

                    3000,3

                    1000,4

                     

                    i.e. you don't need to aggregate per NO_(), then you don't need the aggr() function:

                     

                    create a calculated dimension like

                     

                    =If( [Gross Amount] < 1500, Dual('<1500',1500),

                         If( [Gross Amount] < 3500, Dual('>=1500 <3500',3500),

                              If( [Gross Amount] < 7500, Dual('>=3500 <7500',7500),

                                   If( [Gross Amount] < 15000, Dual('>=7500 <15000',15000),Dual('>=15000 ',20000)))))

                     

                    As expression, use

                    =Count(DISTINCT [NO_()])

                     

                    If you need to aggregate the amounts per NO_(), add the aggr() around the if()

                     

                    =Aggr(

                         If( Sum([Gross Amount])/Count([NO_()] < 1500, Dual('<1500',1500),

                              If( Sum([Gross Amount])/Count([NO_()] < 3500, Dual('>=1500 <3500',3500),

                                   If(Sum([Gross Amount])/Count([NO_()] < 7500, Dual('>=3500 <7500',7500),

                                        If( Sum([Gross Amount])/Count([NO_()]< 15000, Dual('>=7500 <15000',15000),Dual('>=15000 ',20000)))))

                    ,[NO_()] )


                    edit: added the aggregations to the second sample.

                  • Re: Create a calculated dimension by measure
                    Sunny Talwar

                    Stefan correct me if you think I am wrong, but won't we need Aggr() function here for the calculated dimension?

                     

                    =Aggr(If(Sum(Amount)/Count(Order_No) < 200, Dual('<200', 200),

                              If(Sum(Amount)/Count(Order_No) < 500, Dual('>=200 <500', 500), Dual('>=500', 520))), Customer)

                  • Re: Create a calculated dimension by measure
                    Rohit Kumar

                    Hi swuehl,

                    You above answer is the best solution for my problem. Its should be appreciated and saved my lots of time . thanks a lot.