Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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...
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)
)
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!
Was it correct the first time?
No...
Can you list all your dimensions and also what is the expected output for Region in numerical terms?
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!
Why don't you accomplish all these manipulations in the script itself... this will make your life so much easier