5 Replies Latest reply: Mar 7, 2018 9:14 AM by Sofia Vaz RSS

    Issue with a calculated dimension and a aggr function

    Sofia Vaz

      Hello everyone!

       

      I've been using a created dimension in order to organize countries code's (AllocTransfOrigMarketCd) in groups.The expression is this one:

       

      =if(match(AllocTransfOrigMarketCd,'14','15','93'),'AU+CE+CH',

        if(match(AllocTransfOrigMarketCd,'22','23'),'BL+HL+NE',

              if(match(AllocTransfOrigMarketCd,'999','AC','AM','AP','OF','27','.','SET','30','PK','73','GPS','SP','SCO'),'Other',

                  if(match(AllocTransfOrigMarketCd,'91','92','92Z','92L','92M','92O','91Z','91L','91M','91A','91O','92A'),'Overseas',

                      if(match(AllocTransfOrigMarketCd,'17','28'),'UK+IR',

                          AllocTransfOrigMktDescrip)))))

       

      I'll try to explain better what I want with this expression:

       

      Let's say this dimension is called Office. If AllocTransfOrigMarketCd is not mentioned on any match function of Office definition, the name of the group of that field in the dimension Office should be its correspondent AllocTransfOrigMktDescrip.

       

      For example, as AllocTransfOrigMarketCd= 10  is not mentioned, it should be include in a group called PORTUGAL because PORTUGAL is the AllocTransfOrigMktDescrip for  AllocTransfOrigMarketCd= 10.

       

      This expression works pretty fine when I'm using a pivot table with simple measure.

       

      The trouble  starts when I'm using and agregatte function as  a measure in a pivot table.

       

      For example, I have this measure in a pivot table:

       

      =if(getfieldselections(AllocTransfInsDate)>0,

       

      count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId) / aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip),

       

      count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip))

       

       

      Wich means I'm calculating relative weights by aggregating value by AllocTransfOrigMktDescrip.

       

      If I use AllocTransfOrigMktDescrip as a row dimension, everything goes fine… but if I use the calculated dimension "Office", QS shuffles and output something like the image attached.I think that's because I aggregate the measure by AllocTransfOrigMktDescrip… Does anyone know what I can do better?

        • Re: Issue with a calculated dimension and a aggr function
          Sasidhar Parupudi

          It is better if you created a new field in your script using applymap

           

          map_values:

          Mapping Load

          *

          INLINE

          [

          AllocTransfOrigMarketCd,Result

          14,AU+CE+CH

          15,AU+CE+CH

          93,AU+CE+CH

          22,BL+HL+NE

          23,'BL+HL+NE

          .

          .

          .

          ];

          YourTable1:

          Load

          your filed,

          ,AllocTransfOrigMarketCd,

          AllocTransfOrigMktDescrip

          Applymap('map_values',AllocTransfOrigMarketCd,AllocTransfOrigMktDescrip) As NewGroup

          Resident YourTable;

           

          In the front end, you could use NewGroup withouth having to write a complex expression.. hope this helps

           

          Sasi

          • Re: Issue with a calculated dimension and a aggr function
            Digvijay Singh

            If you surround your Aggr with Sum, does it make any difference? -

             

            =if(getfieldselections(AllocTransfInsDate)>0,

             

            count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId) / Sum(aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)),

             

            count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / Sum(aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)))

              • Re: Issue with a calculated dimension and a aggr function
                Sofia Vaz

                Thank you Digvijay! We are on the right way... The pivot table groups the fields as I wish, but the measure output is now incorrect... I'm working on this now! thank you!

                • Re: Issue with a calculated dimension and a aggr function
                  Sofia Vaz

                  Digvijay, just to update you:

                   

                  in order to keep measure right we must add an DISTINCT after the sum functions you've add. Like this:

                   

                  =if(getfieldselections(AllocTransfInsDate)>0,

                   

                    count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)),

                   

                    count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)))

                   

                  thank you, your help was indespensible.