topic Re: Replicating a SUMIFS formula in QlikView in QlikView

Replicating a SUMIFS formula in QlikView

Mon, 30 Jun 2014 12:21:11 GMT

I am trying to replicate a sumifs formula that i would write in Excel. Thus far i have this script:

NUM(sum(QtyReceived),'#,##0')

I want to add criteria from vavious other fields so that it only adds up certain "QtyReceived" figures.

Can someone help?

Many thanks

Re: Replicating a SUMIFS formula in QlikView

its_anandrjs — Mon, 30 Jun 2014 12:27:12 GMT

There are many ways try SET analysis expression or write if expression

Ex:-

NUM(sum( if( Match(Country,'Germany','Australia'), QtyReceived)),'#,##0')

Ex:- With SET

NUM(sum( {<Country ={ 'Germany','Australia' }>} QtyReceived),'#,##0')

This are simple example you can create any type of set expression.

Re: Replicating a SUMIFS formula in QlikView

maxgro — Mon, 30 Jun 2014 12:35:02 GMT

example with

more criteria on one field

more fields

sum( {$<Year = {2000}, Region = {US, SE, DE, UK, FR}>} Sales )

you can find more in online qlik help

or here

Sets Analysis: syntaxes, exemples

Re: Replicating a SUMIFS formula in QlikView

Tue, 22 Mar 2016 16:02:54 GMT

I had a similar issue. I've copied in my formula below. I was just able to use the word "and" between my ifs. My goal was to get my Totals based on Benchmarks='Yes" and the Metric='ED Visits / 1,000'. This accomplished my goal. I tried adding a few more restrictions and it worked as long as everything was separated by "and" and was before the comma.

Sum(if(Benchmarks='Yes' and Metric='ED Visits / 1,000',Totals))

Re: Replicating a SUMIFS formula in QlikView

Kakay_Ranas — Mon, 21 Oct 2019 17:24:35 GMT

Can you help me apply this in color expression? I tried below which didn't work. Thanks in advance.

IF((sum({MEASURE = {'CS_SERVICE_LEVEL'},CONTRACT ={'CAREFIRST_A','CAREFIRST_B'}>} NUMERATOR)/(sum({MEASURE = {'CS_SERVICE_LEVEL'},CONTRACT ={'CAREFIRST_A','CAREFIRST_B'}>}DENOMINATOR))>= .90, Green(270)
, Red(255)