topic Re: Difference between Expression Total And ROW Total in QlikView

Difference between Expression Total And ROW Total

anuradhaa — Fri, 08 Aug 2014 09:17:51 GMT

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

Re: Difference between Expression Total And ROW Total

kaushiknsolanki — Fri, 08 Aug 2014 09:34:13 GMT

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

Re: Difference between Expression Total And ROW Total

anuradhaa — Fri, 08 Aug 2014 09:36:42 GMT

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

Thank you.

Re: Difference between Expression Total And ROW Total

kaushiknsolanki — Fri, 08 Aug 2014 09:40:46 GMT

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

Re: Difference between Expression Total And ROW Total

anuradhaa — Fri, 08 Aug 2014 09:48:31 GMT

Thank You,

It works

Re: Difference between Expression Total And ROW Total

anuradhaa — Fri, 08 Aug 2014 09:54:42 GMT

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

Thank you

Re: Difference between Expression Total And ROW Total

kaushiknsolanki — Fri, 08 Aug 2014 09:59:33 GMT

HI,

I am not clear, kindly tell in detail.

Regards,

Kaushik Solanki

Re: Difference between Expression Total And ROW Total

anuradhaa — Fri, 08 Aug 2014 10:05:22 GMT

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.

Re: Difference between Expression Total And ROW Total

kaushiknsolanki — Fri, 08 Aug 2014 10:12:18 GMT

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

Re: Difference between Expression Total And ROW Total

anuradhaa — Fri, 08 Aug 2014 10:36:28 GMT

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.

Re: Difference between Expression Total And ROW Total

kaushiknsolanki — Fri, 08 Aug 2014 15:37:26 GMT

Kindly share sample file.

Regards,

Kaushik Solanki