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

# Totals not following the set expression

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

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

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

• ###### Re: Totals not following the set expression

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)

)

• ###### Re: Totals not following the set expression

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!

• ###### Re: Totals not following the set expression

Was it correct the first time?

• ###### Re: Totals not following the set expression

No...

• ###### Re: Totals not following the set expression

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

• ###### Re: Totals not following the set expression

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!

• ###### Re: Totals not following the set expression

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