Creating a Measure with Multiple Conditions

[#1]

Hi,

I'm new to SQL and QlikSense and I am looking to create a new measure/field based on two conditions but I can't seem to get it to work. I'm using it in a sporting environment and want to make a new measure called 'Successful Forward Pass'. I have two measures called 'Pass Outcome' and 'Pass Direction' already and I've tried IF statements like:

if(Count(DIRECTION)='Forward' and Count([PASS OUTCOME])= 'Successful' as 'Successful Forward Pass'))

Basically if the pass is forward and successful. Any help would be greatly appreciated.

Thanks

[rwunderlich]

It would be:

count( if(DIRECTION='Forward' AND [PASS OUTCOME]='Successful') )

-Rob

[MK_QSL]

Try without COUNT... only IF(Direction = 'Forward' and If([Pass Outcome] = 'Successful', 'Successful Forward Pass'))

Change Field name as per case sensitive

[Anonymous]

Count() will return a number...How you can compare it with 'Forward' or 'Success'???

[ogster1974 · Accepted solution]

To filter, group etc. in Qlik products you need to get familiar with Set Analysis.  Please find attached a document I found useful when I first started out and still refer back to.

[#6]

I managed to figure it out eventually through much trying. The fields for 'Pass Outcome' and 'Pass Direction' were created by merging separate fields from delimited data...

'Pass Outcome:Successful' 'Pass Outcome:Unsuccessful' = Pass Outcome for example. Instead of merging them I left them as separate fields and used this formula:

sum([Direction:Forward] / [Pass Outcome:Successful])

If I'm honest it seems quite illogical but I've checked the data and it's absolutely correct. I initially tried 'and' instead of '/' and realised the numbers were correct but obviously negatives (e.g. -109 instead of 109). Bear in mind I'm completely new to SQL and I'm essentially teaching myself.

@Andy Weir thanks for the reply I will definitely take a look at set analysis. Many thanks for the replies.