Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted
Master III
Master III

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

Highlighted
Creator II
Creator II

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

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