Totals not following the set expression

[Anonymous]

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

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

[Anonymous]

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...

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

)

[Anonymous]

Thank you Sunny!! Thats almost right! Now, the only issue is with the total by region... do you know what can be done? Thank you!

[sunny_talwar]

Was it correct the first time?

[Anonymous]

No...

[sunny_talwar]

Can you list all your dimensions and also what is the expected output for Region in numerical terms?

[Anonymous]

Hi Sunny! Thanks for your help.

Direction and Office are calculated values, with this expressions:

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

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

The abs values i'm working with and the correspondent relative weight are this:

Thank you!

[sunny_talwar]

Why don't you accomplish all these manipulations in the script itself... this will make your life so much easier