Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
could you please help me to correct above expression to get correct total.
Thank you.
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
Thank You,
It works
Could you please tell me how to play in a pivot table in here.
Thank you
HI,
I am not clear, kindly tell in detail.
Regards,
Kaushik Solanki
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.
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
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.