Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
yohann24
Contributor III
Contributor III

Subtracting row value from total column avg in straight table

Hi, In the last column I am trying to populate values from SM% Column by subtracting the row value minus total avg i.e. 73.52-60.47 =, like wise for rest of the rows. can someone please help me on this.

SM% = SM$/Column(2)

** I have selected one Prod hier lvl0 here but we have similarly having 5 values under this lvl 0. Under each lvl 0 we have few lvl 3 values as shown here. so the calculation is driven by lvl0 & lvl3 & Pole. lvl 3s is under lvl0.

Straight_SUB.png

13 Replies
Anil_Babu_Samineni

What if we go like below for SM% vs BU Avg

If(POLE_ILT = 'ALL REGIONS', if(

IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} USD_NET_SALES)/1000000)=0,0,

((IF(POLE_ILT = 'ALL REGIONS', Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)},Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} USD_NET_SALES_MARGIN)/1000000))/

(IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} USD_NET_SALES)/1000000))))) - Avg(If(POLE_ILT = 'ALL REGIONS', if(

IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} TOTAL USD_NET_SALES)/1000000)=0,0,

((IF(POLE_ILT = 'ALL REGIONS', Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)},Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} TOTAL USD_NET_SALES_MARGIN)/1000000))/

(IF(POLE_ILT = 'ALL REGIONS', 

Sum({< Transaction_Type = {'Billings'}, Year_Fiscal = {$(#vFYear)}, Quarter_Fiscal = {$(#vFQuarter)}, POLE = >} TOTAL USD_NET_SALES)/1000000))))))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
yohann24
Contributor III
Contributor III
Author

SM%SM% vs BU Avg
60.47%27.11%
73.52%16.44%13.050473912730300%
18.68%-38.40%-54.840223894839200%
100.00%42.92%39.529940473609100%
100.00%42.92%26.479466560878800%
41.58%-15.50%22.896853587444400%
46.15%-10.93%-53.848847870958000%
62.43%5.34%-37.574783948372300%
41.41%-15.68%

-0.171061216398277%

its appearing like this.. i have downloaded to an excel and compared with the new formula.

Anil_Babu_Samineni

Are you not seeing this after using my expression?

Highlight - Ask me if you want to remove the image from threat security.

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
yohann24
Contributor III
Contributor III
Author

yes you are rt. I am not able to see these figures as shown in the pic shared by you rather the figures are something which i shared in my earlier response.