Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

EXPRESSION MODIFICATION

Dear Experts,

I have a pivot table which looks like below,

BCOD MO_NM CLA_CODE EFF_MONTH JanFebMarAprMayJunJulAugSepOct
B100MOTORM3148916158661164360158743158506140203186970202715155078178589
M437313-88923112722245386123106635182910307103021120
MC2117618217575319130491858664197562017989442124056225474724743382893513
Total 2303847 2325522 2108536 2039653 2172738 1970213 2346209 2548491 2700447 3093222
NON_MOTOREN116400-6600034315588003000030155034000320952665079580
FR1453132456711997084460-637914531020684256702401728142
HH80135125328759232523690-10522046977-7215-1703410641
MS4865216056465028156041218236178873219810178535-21717-18022
PP43439389653456517289217262667737848430332947632494
SS52700282003700-282002820056400-84600479602996040460
TC4676114534518972913461818981849486279052166747230740255439
TT3300-------625043450
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
sushil353
Master II
Master II

Hi,

Try this expression:

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


HTH

Sushil

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Not applicable
Author

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 JanFebMarAprMayJunJulAugSepOct
B100MOTORM30.00630.00670.00700.00670.00670.00590.00790.00860.00660.0076
M40.0016-0.00040.00130.00090.00160.00130.00150.00390.00300.0009
MC0.08970.09220.08100.07870.08370.07620.09000.09550.10480.1226
Total 0.0976 0.0985 0.0893 0.0864 0.0920 0.0835 0.0994 0.1079 0.1144 0.1310
NON_MOTOREN0.0233-0.01320.00690.01180.00600.06040.00680.00640.00530.0159
FR0.02910.00490.02400.0169-0.00130.02910.00410.00510.04810.0016
HH0.00020.00700.00660.01850.0047-0.02110.0094-0.0014-0.00340.0021
MS0.00970.03210.01300.03120.04370.03580.04400.0357-0.0043-0.0036
PP0.00870.00780.00690.00350.00430.00530.00760.00860.00590.0065
SS0.01050.00560.0007-0.00560.00560.0113-0.01690.00960.00600.0081
TC0.00940.02910.03800.02690.03800.00990.05590.03340.04620.0511
TT0.00070.00000.00000.00000.00000.00000.00000.00000.00130.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.

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
chhavi376
Creator II
Creator II

Hi Priyantha,

Please have a look at the attached QV.

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

I quess this is what you wanted:

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

thath gives you this result:

Capture.PNG

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

kind regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.