SUMIF in QlikView with Dynamic Variable

[Anonymous]

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?

[devarasu07]

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?

[Anonymous]

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]

Hi,

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

[Anonymous]

[devarasu07]

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

[Anonymous]

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

[devarasu07]

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;

Thanks

Deva

[Anonymous]

AMAZING.  Thank you!!