topic Re: SUMIF in QlikView with Dynamic Variable in QlikView

SUMIF in QlikView with Dynamic Variable

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi- I'm new to Qlikview and I am trying to replicate an expression I have in an excel table:

=SUMIFS($A$2:$A$433360,A2,$H$2:$H$433360)

Basically, this is a SUMIF function with a dynamic variable. Any idea how to recreate this in Qlikview?

Re: SUMIF in QlikView with Dynamic Variable

devarasu07 — Tue, 23 Jan 2018 18:50:36 GMT

Hi,

Sum(if( condition , ouptput))

let say Qty is the measure and Product is the dimension. so the requirement is to show /list the product never sold

so the expression u can write like this

sum ( if ( sum(Qty)<=0, Product )

also u can check HIC suggestion

When is it best to use sum(if()) over Set Analysis in an expression?

Re: SUMIF in QlikView with Dynamic Variable

Anonymous — Tue, 23 Jan 2018 18:59:13 GMT

Thanks for your response. I need the "condition" to be dynamic such that for line A167, the function sums every value in column H where the value in column A = A167.

I need this calculation to be done in the script. I can attached an example excel file if that would be helpful. Thanks!!

Re: SUMIF in QlikView with Dynamic Variable

devarasu07 — Tue, 23 Jan 2018 19:09:12 GMT

Hi,

yes we can do that in back end script as well, can u share the mock data and expected output. tks

Re: SUMIF in QlikView with Dynamic Variable

Anonymous — Tue, 23 Jan 2018 20:51:26 GMT

Re: SUMIF in QlikView with Dynamic Variable

devarasu07 — Tue, 23 Jan 2018 23:50:00 GMT

Hi,

u want to Calculate "Total Credit Hours" using the sum if function? if so then qlikview will do aggregation by default nature. so just use that sum([Credit Hours]) like below,

Thanks,

Deva

Re: SUMIF in QlikView with Dynamic Variable

Anonymous — Wed, 24 Jan 2018 01:54:26 GMT

Thanks Deva! However, what I want is to create new dimension in the script such that replicates column C above.

Re: SUMIF in QlikView with Dynamic Variable

devarasu07 — Wed, 24 Jan 2018 02:00:16 GMT

Hi,

Yes we can do that,

try like this

Data:

LOAD * INLINE [

Student, Credit Hours

A, 3

A, 6

A, 3

A, 2

B, 3

B, 5

B, 4

C, 3

C, 4

C, 5

];

left join

load Student,sum([Credit Hours]) as [Total Credit Hours] Resident Data group by Student;

Thanks

Deva

Re: SUMIF in QlikView with Dynamic Variable

Anonymous — Wed, 24 Jan 2018 16:55:38 GMT

AMAZING. Thank you!!