Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
There are many ways try SET analysis expression or write if expression
Ex:-
NUM(sum( if( Match(Country,'Germany','Australia'), QtyReceived)),'#,##0')
Or
Ex:- With SET
NUM(sum( {<Country ={ 'Germany','Australia' }>} QtyReceived),'#,##0')
This are simple example you can create any type of set expression.
Read more about the SET analysis here
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
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))
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)