2 Replies Latest reply: Mar 9, 2018 10:31 AM by Sofia Vaz RSS

    Problem with totals when working with relative values

    Sofia Vaz

        Hi all,

       

      I'm using a pivot tible with two calculated dimensions as rows and two fields as columns.

       

      The measure expression is this one:

       

      =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)))

       

      Does anybody knows why are the totals badly calculated? How can I solve that?