Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sofiavaz
Contributor

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

9 Replies

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)

sofiavaz
Contributor

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

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)

)

sofiavaz
Contributor

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!

3.png

Re: Totals not following the set expression

Was it correct the first time?

sofiavaz
Contributor

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?

sofiavaz
Contributor

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:

Abs.pngRel.png

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

Capture.PNG

Community Browser