topic Re: Expression: Sumif in QlikView

Expression: Sumif

Mon, 25 Jan 2016 12:09:21 GMT

Hi All - Newbie to forum (and qlikview)

I have two tables Tab_1 and Tab_2, which are qualified (so that i can show all values in field even when selection is applied):

Tab_1.Column1:

Sales,

Costs,

Other

Tab_2.Column1:

Costs,

Other

i would like to insert a straight table in qv - using tab_2 as dimension column, and sum values where Tab_1.column1 values = Tab_2.Column1 values

I have been looking around and have only got as far as the following - which seems to summing total value column in Tab1

Sum({$<[Tab1_Column1]=([Tab2_column1])>}Tab1_Value)

Any help would be much appreciated

Re: Expression: Sumif

swuehl — Mon, 25 Jan 2016 12:15:33 GMT

I think you are showing only partial picture here, how are the two tables linked to each other?

Could you post a small sample QVW?

Re: Expression: Sumif

Mon, 25 Jan 2016 12:18:30 GMT

swuehl,

many thanks for your reply:

I have two tables: Tab_1 and Tab_2

Tab_1.Capture Point	Value
Sales	10
Costs	5
Other	2

Tab_2 is:

1) unlinked/Qualified (Very Important)

2) was created as an inline table

Tab_2.Capture Point
Costs
Other

I have inserted a straight table in qv - the dimensions of which are those from Tab_2.

I would like to sum from Tab_1 where values in Tab_1.Capture Point = values in the straight table (Tab_2.Capture Point)

I would prefer to use the expression builder as a means of achieving this - basically, i need a sumif(tab_1.Capture Point,tab_2.capture point,tab_1.value)

Please advise

Many thanks

Re: Expression: Sumif

swuehl — Mon, 25 Jan 2016 12:33:28 GMT

I would rather link both tables (or join them), so you can solve your table dimension filtering by just using the data model.

(there are other methods to show all values, even when you made selections, using set analysis).

But even if you don't change your data model, using Tab_2.Capture Point as dimension and this as expression

=Sum(If([Tab_2.Capture Point] = [Tab_1.Capture Point], Value))

should work.

Tab_2.Capture Point	Sum(If([Tab_2.Capture Point] = [Tab_1.Capture Point], Value))
	7
Costs	5
Other	2

Re: Expression: Sumif

Mon, 25 Jan 2016 12:40:19 GMT

Perfect - didnt think the answer was use of an if statement

Many thanks for your help today