# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## EXPRESSION MODIFICATION

Dear Experts,

I have a pivot table which looks like below,

 BCOD MO_NM CLA_CODE EFF_MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct B100 MOTOR M3 148916 158661 164360 158743 158506 140203 186970 202715 155078 178589 M4 37313 -8892 31127 22245 38612 31066 35182 91030 71030 21120 MC 2117618 2175753 1913049 1858664 1975620 1798944 2124056 2254747 2474338 2893513 Total 2303847 2325522 2108536 2039653 2172738 1970213 2346209 2548491 2700447 3093222 NON_MOTOR EN 116400 -66000 34315 58800 30000 301550 34000 32095 26650 79580 FR 145313 24567 119970 84460 -6379 145310 20684 25670 240172 8142 HH 801 35125 32875 92325 23690 -105220 46977 -7215 -17034 10641 MS 48652 160564 65028 156041 218236 178873 219810 178535 -21717 -18022 PP 43439 38965 34565 17289 21726 26677 37848 43033 29476 32494 SS 52700 28200 3700 -28200 28200 56400 -84600 47960 29960 40460 TC 46761 145345 189729 134618 189818 49486 279052 166747 230740 255439 TT 3300 - - - - - - - 6250 43450 Total 457366 366766 480182 515333 505291 653076 553772 486825 524497 452183

In this presentation only expression is SUM(AMOUNT_2016)

My problem is how can  i modify above expression to achieve percentages from the total  instead of above figures. then the total must be 100.

Rgds,

Priyantha.

6 Replies
Honored Contributor II

## Re: EXPRESSION MODIFICATION

Hi,

Try this expression:

SUM(AMOUNT_2016)/SUM(Total<CLA_CODE>AMOUNT_2016)

HTH

Sushil

Honored Contributor III

## Re: EXPRESSION MODIFICATION

It is important also to determine dimensionality - so the question is whether your percentage should be calculate from subtotal on CLA_CODE or maybe MO_NM level or on any other level. Then ultimetly change value in those brackets

<CLA_CODE> as per Sushil's post

regards

Lech

Not applicable

## Re: EXPRESSION MODIFICATION

Dear Sushi,

I modified with your assistance and got the following expression.

ROUND(SUM(AMOUNT_2016)/SUM(Total<MO_NM>AMOUNT_2016),0.0001)

But expected output does not achieved.

Look at the resulted output.

 BCOD MO_NM CLA_CODE EFF_MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct B100 MOTOR M3 0.0063 0.0067 0.0070 0.0067 0.0067 0.0059 0.0079 0.0086 0.0066 0.0076 M4 0.0016 -0.0004 0.0013 0.0009 0.0016 0.0013 0.0015 0.0039 0.0030 0.0009 MC 0.0897 0.0922 0.0810 0.0787 0.0837 0.0762 0.0900 0.0955 0.1048 0.1226 Total 0.0976 0.0985 0.0893 0.0864 0.0920 0.0835 0.0994 0.1079 0.1144 0.1310 NON_MOTOR EN 0.0233 -0.0132 0.0069 0.0118 0.0060 0.0604 0.0068 0.0064 0.0053 0.0159 FR 0.0291 0.0049 0.0240 0.0169 -0.0013 0.0291 0.0041 0.0051 0.0481 0.0016 HH 0.0002 0.0070 0.0066 0.0185 0.0047 -0.0211 0.0094 -0.0014 -0.0034 0.0021 MS 0.0097 0.0321 0.0130 0.0312 0.0437 0.0358 0.0440 0.0357 -0.0043 -0.0036 PP 0.0087 0.0078 0.0069 0.0035 0.0043 0.0053 0.0076 0.0086 0.0059 0.0065 SS 0.0105 0.0056 0.0007 -0.0056 0.0056 0.0113 -0.0169 0.0096 0.0060 0.0081 TC 0.0094 0.0291 0.0380 0.0269 0.0380 0.0099 0.0559 0.0334 0.0462 0.0511 TT 0.0007 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0013 0.0087 Total 0.0916 0.0734 0.0961 0.1032 0.1012 0.1307 0.1109 0.0975 0.1050 0.0905

with this calculation total raw must have the 1.0000 as the answer in both Motor and Non_Motor

Can you please find the mistake i have done here.

Honored Contributor III

## Re: EXPRESSION MODIFICATION

Hi,

From what i am counting the total is 1 for both - so there is no mistake, unless you want total for each month as well, dont you?

This would be then different granularity <CLA_CODE>

hth

Lech

Contributor II

## Re: EXPRESSION MODIFICATION

Hi Priyantha,

Please have a look at the attached QV.

Honored Contributor III

## Re: EXPRESSION MODIFICATION

Hi,

I quess this is what you wanted:

=sum(AMOUNT)/SUM(Total<MO_NM, EFF_MONTH>AMOUNT)

thath gives you this result:

If you need different calculation, maybe provide and XLS example what you're trying to achieve.

kind regards

Lech