sum of all rows by Organization code

Mon, 29 May 2017 09:02:20 GMT

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.

Re: sum of all rows by Organization code

PrashantSangle — Mon, 29 May 2017 12:05:00 GMT

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,

Re: sum of all rows by Organization code

Tue, 30 May 2017 04:54:29 GMT

=($(=

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?

Re: sum of all rows by Organization code

vinieme12 — Tue, 30 May 2017 07:35:38 GMT

try like

$(=

sum( AGGR(

my Exp

, FieldnametoAGGR ) )

)

Re: sum of all rows by Organization code

Tue, 30 May 2017 08:06:24 GMT

thanks for your suggetion.

but it will give wrong output

can you please suggest another one?

Re: sum of all rows by Organization code

vinieme12 — Tue, 30 May 2017 09:01:16 GMT

it's easier if you can post a sample app

Re: sum of all rows by Organization code

Tue, 30 May 2017 09:25:32 GMT

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

topic Re: sum of all rows by Organization code in QlikView

sum of all rows by Organization code

Re: sum of all rows by Organization code

Re: sum of all rows by Organization code

Re: sum of all rows by Organization code

Re: sum of all rows by Organization code

Re: sum of all rows by Organization code

Re: sum of all rows by Organization code