9 Replies Latest reply: Mar 23, 2018 12:13 PM by Sunny Talwar RSS

    Totals not following the set expression

    Sofia Vaz

      Hi all,

       

      Do you know why are totals not following the set expression?

       

      The expression is this one:

       

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

       

      You can the see the output at tha imaged attached. Individual results are well calculated but, for example, for Europe-ATF-APPROV i shouldn't have 125%...

       

      Any help will be appreciated!

       

      Thank you,

       

      Sofia

        • Re: Totals not following the set expression
          Sunny Talwar

          Not sure, but may be this

           

          =Count({<AllocTransfTransfStat -= {"CANCEL"}, AllocTransfInsDateYear = {"$(=Year(Today()))"}, AllocTransfInsDateWeek = {"$(vCurrentInsDateWeek)"}>} AllocTransfAllocTransfId)

          /

          Count(TOTAL <AllocTransfOrigMktDescrip, AllocTransfOrigMillCd> {<AllocTransfTransfStat -= {"CANCEL"}, AllocTransfInsDateYear = {"$(=Year(Today()))"}, AllocTransfInsDateWeek ={"$(vCurrentInsDateWeek)"}>} AllocTransfAllocTransfId)

            • Re: Totals not following the set expression
              Sofia Vaz

              Thank you Sunny. I've tried that and attached the output... it seems that it is calculating the totals correctly now, but the other values are wrong... 2.png

                • Re: Totals not following the set expression
                  Sunny Talwar

                  May be use Dimensionality() and/or SecondaryDimensionality() functions here

                   

                  If(Dimensionality() = 2,

                   

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


                  Count({<AllocTransfTransfStat -= {"CANCEL"}, AllocTransfInsDateYear = {"$(=Year(Today()))"}, AllocTransfInsDateWeek = {"$(vCurrentInsDateWeek)"}>} AllocTransfAllocTransfId)

                  /

                  Count(TOTAL <AllocTransfOrigMktDescrip, AllocTransfOrigMillCd> {<AllocTransfTransfStat -= {"CANCEL"}, AllocTransfInsDateYear = {"$(=Year(Today()))"}, AllocTransfInsDateWeek ={"$(vCurrentInsDateWeek)"}>} AllocTransfAllocTransfId)

                  )