Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sofiavaz
Contributor

Totals when working with % values

Hi all,

In a pivot, totals % are not working well... (picture attached)

Measure expression:

=if(getfieldselections(AllocTransfInsDate)>0,

count({<AllocTransfTransfStat-={"CANCEL"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat-={"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip,AllocTransfOrigMillCd)),

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

Calculated dimensions:

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

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

Does anyone know what I might be doing wrong?

Thank you!

2 Replies
Digvijay_Singh
Honored Contributor III

Re: Totals when working with % values

Can you share sample app or sample data to see whats wrong with the formula?

You may try this without equal sign, not sure though -

AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}

sofiavaz
Contributor

Re: Totals when working with % values

Thank you Digijay. I've tried without the = sign but the output remains the same.

I'm not able to share sample data...

Community Browser