2 Replies Latest reply: Mar 21, 2018 5:39 AM by Sofia Vaz RSS

    Totals when working with % values

    Sofia Vaz

      Hi all,

       

      In a pivot, totals % are not working well... (picture attached)

       

      Measure expression:

       

      =if(getfieldselections(AllocTransfInsDate)>0,

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

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

       

       

      Calculated dimensions:

       

      1. Direction:

        =if(match(AllocTransfOrigMarketCd,'GPS','26', '10','11','12','13','14','15','17','22','23','24','25','28','29','93','94','93A','PK'), 'Europe', 

      if(match(AllocTransfOrigMarketCd, '46'),'USA',

                  if(match(AllocTransfOrigMarketCd,'16','92','92Z','92A','92M','92L','92O', '16I'), 'International',

                      'Other')))

       

      1. Office:

      =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',

                                  if(match(AllocTransfOrigMarketCd,'92Z','26','12','13','25','16I','24','94','93A','10','29','11','16','46'),AllocTransfOrigMktDescrip))))))

       

       

      Does anyone know what I might be doing wrong?

       

      Thank you!