Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

satwood10
New Contributor

SUMIF in QlikView with Dynamic Variable

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?

8 Replies
devarasu07
Honored Contributor II

Re: SUMIF in QlikView with Dynamic Variable

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?

satwood10
New Contributor

Re: SUMIF in QlikView with Dynamic Variable

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!!

devarasu07
Honored Contributor II

Re: SUMIF in QlikView with Dynamic Variable

Hi,

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

satwood10
New Contributor

Re: SUMIF in QlikView with Dynamic Variable

Example File.JPG

devarasu07
Honored Contributor II

Re: SUMIF in QlikView with Dynamic Variable

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,

Capture.JPG

Thanks,

Deva

satwood10
New Contributor

Re: SUMIF in QlikView with Dynamic Variable

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

devarasu07
Honored Contributor II

Re: SUMIF in QlikView with Dynamic Variable

Hi,

Yes we can do that,

try like this

Data:

LOAD * INLINE [

    Student, Credit Hours

    A, 3

    A, 3

    A, 6

    A, 3

    A, 2

    B, 3

    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;

Capture.JPG

Thanks

Deva

satwood10
New Contributor

Re: SUMIF in QlikView with Dynamic Variable

AMAZING.  Thank you!!