sum of all rows by Organization code

[#1]

I Have a column which have data derived by the expression.i need to sum of all rows by Organization code wise,can any one have idea?

below is my expession.

($(=sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),0,0),'MMM-YY')))'}>}ACTUAL))

problem with above expression is it will give sum of all rows which display based on selection but i need sum of all rows organization wise.

[PrashantSangle]

in chart you want to achieve it???

try with TOTAL keyword like below

sum(TOTAL<org_code> {<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),0,0),'MMM-YY')))'}>}ACTUAL)

Regards,

[#3]

=($(=

sum(aggr((sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),0,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),1,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),2,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),3,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),4,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),5,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),6,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),7,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),8,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),9,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),10,0),'MMM-YY')))'}>}ACTUAL)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),11,0),'MMM-YY')))'}>}ACTUAL))

/

(sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),0,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),1,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),2,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),3,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),4,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),5,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),6,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),7,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),8,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),9,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),10,0),'MMM-YY')))'}>}PRODACT)

+

sum({<PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&'APR'&'-'&(P_YEAR + If(cal_month>=4, -1, -2)), ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),11,0),'MMM-YY')))'}>}PRODACT))

*

sum({<RATE_PERIOD = {'$(=upper(date(AddMonths(Date(Date#(concat(1&'-'&P_MONTH&'-'&P_YEAR, ',' ),'DD-MMM-YYYY'),'DD-MM-YYYY'),0,0),'MMM-YY')) )'}>}RM_RATE),CODE))

))

This is my expression which gives output as below

Image 1) Row wise values

Image 2) Sum of all rows

now i want sum of all rows values.and it is achieved in my expression as i am putting ($(= my Exp )) and in all rows sum displayed as 125.40 .but it is worked when i select particular organization code.but i select multiple organization code then above mentioned exp  "($(= my Exp ))" sum up all rows of all organization.

can i get organization wise sum of all rows pertaining to specific organization code?

[vinieme12]

try like

$(=

sum( AGGR(

my Exp

, FieldnametoAGGR )  )

)

[#5]

thanks for your suggetion.

but it will give wrong output

can you please suggest another one?

[vinieme12]

it's easier if you can  post a sample app

[#7]

HI THANKS FOR THE HELP

i already puted the screen shot of the app.

sorry but i can not post because original file was on server and i couldn't download it.

for your better understand ,below is the scenario.

($(= myexp )) this will sum up all row values that "myexp" evaluates.

but based on the selection if there are 20 rows in the result out of them 10 are for org1,5 for org2 and 5 for org3.

now above expression give sum of all 20 rows and i want sum of all rows org wise