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: 
anuradhaa
Partner - Creator II
Partner - Creator II

Difference between Expression Total And ROW Total

Hi All,

I use below expression to calculate charges. It has below logic

=money((if(OWNER='ABC' or OWNER='XYZ',0,

(if(((OWNER='YYY' or OWNER='ZZZ') and (Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')))

,sum(YYY_Total),(if((OWNER='YYY'),sum(YYY_Total),(if((Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')),

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),

((((sum(COUNTA)-13000)*.083)+1300)*190.6),((((sum(COUNTA)-22000)*.071)+2047)*190.6))))) ,

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1010)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),((((sum(COUNTA)-13000)*.0838)+1313)*190.6),

((((sum(COUNTA)-22000)*.0715)+2067.2)*190.6))))))))))))))

So I use this in straight Table to get separate values based on OWNER,

So the individual values are correct according to logic, but the Total is incorrect when i select expression total.

Here i use same expression in text object to get total sum of values. So i need to get actual sum of all owners, but now the display value is incorrect. please help me to solve this and get correct Total.

Thanks

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

     Change your expression as

     Sum(Aggr(

money((if(OWNER='ABC' or OWNER='XYZ',0,

(if(((OWNER='YYY' or OWNER='ZZZ') and (Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')))

,sum(YYY_Total),(if((OWNER='YYY'),sum(YYY_Total),(if((Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')),

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),

((((sum(COUNTA)-13000)*.083)+1300)*190.6),((((sum(COUNTA)-22000)*.071)+2047)*190.6))))) ,

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1010)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),((((sum(COUNTA)-13000)*.0838)+1313)*190.6),

((((sum(COUNTA)-22000)*.0715)+2067.2)*190.6)))))))))))))), Dimension_Name))

     Instead of Dimension_Name put all the dimensions which you have in your chart.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

10 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Expression total is not the sum of rows, because when you define expression as total, QlikView Calculates the expression for all the values and shows that value as total.

     And for total of rows, it just adds the values of all rows and shows you the result.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
anuradhaa
Partner - Creator II
Partner - Creator II
Author

could you please help me to correct above expression to get correct total.

Thank you.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

     Change your expression as

     Sum(Aggr(

money((if(OWNER='ABC' or OWNER='XYZ',0,

(if(((OWNER='YYY' or OWNER='ZZZ') and (Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')))

,sum(YYY_Total),(if((OWNER='YYY'),sum(YYY_Total),(if((Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')),

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),

((((sum(COUNTA)-13000)*.083)+1300)*190.6),((((sum(COUNTA)-22000)*.071)+2047)*190.6))))) ,

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1010)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),((((sum(COUNTA)-13000)*.0838)+1313)*190.6),

((((sum(COUNTA)-22000)*.0715)+2067.2)*190.6)))))))))))))), Dimension_Name))

     Instead of Dimension_Name put all the dimensions which you have in your chart.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
anuradhaa
Partner - Creator II
Partner - Creator II
Author

Thank You,

It works

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Could you please tell me how to play in a pivot table in here.

Thank you

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

I am not clear, kindly tell in detail.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
anuradhaa
Partner - Creator II
Partner - Creator II
Author

I'm using pivot table also. I want to get individual totals and its sum in there. So sum( rows) inactive in pivot table.

please tell me how to get sum of row in pivot table too.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Go to Presentation tab, here you will see the dimensions, when you click on that you will find an option as "Partial Sum". Click on that.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
anuradhaa
Partner - Creator II
Partner - Creator II
Author

In my pivot table i have three Dimensions,

Year,Month , owner

In my straight table i only have one Dimension -  Owner. also my text object i use Dimension -  OWNER.


So i want to use above expression in my pivot table to calculate charges. here i only activate partial sum for Owner.(3rd dimension)


The partial sum is incorrect there.