topic Re: sum multiple columns in straight table in QlikView

sum multiple columns in straight table

Wed, 24 Sep 2014 07:15:07 GMT

Hi, how do I sum multiple columns in straight table? I want to create column 😧

A	B	C	D
2	6	5	12
4	3	7	14
1	7	8	16
5	9	2	16

Thanks.

Re: sum multiple columns in straight table

MK_QSL — Wed, 24 Sep 2014 07:46:32 GMT

RangeSum(A,B,C)

Re: sum multiple columns in straight table

dirk_konings — Wed, 24 Sep 2014 07:57:11 GMT

=++

Expression labels are reused

Re: sum multiple columns in straight table

jagan — Wed, 24 Sep 2014 10:12:42 GMT

Hi,

Try like this

=[Expression Name1] + [Expression Name2] + [Expression Name3]

=Column(1) + Column(2) + Column(3)

= RangeSum(A, B, C)

Hope this helps you.

Regards,

Jagan.

Re: sum multiple columns in straight table

dgreenberg — Fri, 24 Feb 2017 15:18:50 GMT

Jagan that's right but you run into trouble if you have an unknown number of columns.

For example I take a rolling 24 months but hide the column if there is no data so it's not Column(1)+...Column(24)

If you go to far and take a column that doesn't exist it shows a -

Example

showing Nov-2016 - Jan 2016 if I take Column(1) + Column(2) it's good but if I take Column(1)+Column(2) + column(16) it returns a -.

Same goes for if using column names which are expressions vDateSubmitted24 through vDateSubmitted1.

So what I need to do is figure out the column # in a straight table for this expression and use that to determine the max column # to sum.

I don't think you can get the column # from a straight table - I haven't been able to figure it out

Rangesum works/fails in same manner.

Re: sum multiple columns in straight table

dgreenberg — Fri, 24 Feb 2017 15:32:42 GMT

Ha answered my own question.

wrap the column with an alt and if it's a non existent column or the sum column it will still work.

alt(column(1),0) + alt(column(2),0) + alt(column(3),0) + alt(column(4),0) + alt(column(5),0) + alt(column(6),0) + alt(column(7),0) + alt(column(8),0) + alt(column(9),0) +alt(column(10),0) + alt(column(11),0) + alt(column(12),0) + alt(column(13),0) + alt(column(14),0) + alt(column(15),0) + alt(column(16),0) + alt(column(17),0) + alt(column(18),0)

Re: sum multiple columns in straight table

dgreenberg — Fri, 24 Feb 2017 15:44:39 GMT

Ok but something odd is happening with the above.

My result is 2x what it should be because it's somehow including that column itself (the total column).

Yet if I wrap it in ( )/2 I get a different result that is not correct.

However if I add another expression as =TOTAL //the name of the total column it works.

I can hide the TOTAL column on the presentation tab so I have a work around so far.

Just not sure how I am going to get the average.

When looking at my table below keep in mind this is just what our data set contains the months could be This month (Feb 2017) going back 24 months. Notice how the first Total column is 2x what it should be. That's the one that says =alt(column(1),0) + alt(column(2),0) .... The second Total column says =Total which matches the name of the previous column. Very odd.

Re: sum multiple columns in straight table

dgreenberg — Fri, 24 Feb 2017 16:14:57 GMT

Figured out average.

Not elegant but here goes:

=Total/(Year(max([Date Submitted]))*12 + Month(max([Date Submitted]))

-(Year(min([Date Submitted]))*12 +month(min([Date Submitted])))

)+1)

Re: sum multiple columns in straight table

Mon, 17 Jul 2017 11:10:09 GMT

I need to calculate the final balance based on Min Year start and Max Year start . the date format is in the MMM-YYYY form. I'm writing the below syntax. I know somewhere is wrong. can you please guide me in writing the new syntax.

= sum({<new_date={">=$(vYearStart))<=$(vYearMax)"},(sum(DBAL)-sum(WRITE_OFF)-sum(SECURED_RECOVERY)))

Thanks in advance.

Sowjanya

Re: sum multiple columns in straight table

BrightFuture — Thu, 29 Nov 2018 13:57:15 GMT

Hey there, 

You could do it in the script by using the following way:

Data:
LOAD A, 
     B,
     C,
     Num#(A ,'##.###') + Num#(B ,'##.###') + Num#(C ,'##.###') as D
FROM
[YOUR PATH]
 (ooxml, embedded labels, table is sheet1);

This will sum the elements in each row of A, B, and C and will put it into D for you! Then you could use from "New object Sheet" --> "Table Box" to show your A, B, C, and D columns.