topic Re: Multiple Criteria in one formula (similar to a SUMIFS statement)?? in QlikView

Multiple Criteria in one formula (similar to a SUMIFS statement)??

cmccafferty — Wed, 24 Apr 2013 09:37:38 GMT

Can more than one criteria be applied? So that it works a bit like SUMIFS on Excel.

So for example:

='Single Selling Price : ' & num(max ([Cost PP] where [PriceTypeDescription] = 'Single', and [Ticket Category] = 'First Class', and [Airline] = 'Qantas' ), '£#,##0', '.' , ',' ) so that I only get returned the single price.

Many thanks

Chris

Re: Multiple Criteria in one formula (similar to a SUMIFS statement)??

Gysbert_Wassenaar — Wed, 24 Apr 2013 10:03:22 GMT

Something like this:

='Single Selling Price : ' & num(max({< [PriceTypeDescription]={'Single'},[Ticket Category] = {'First Class'},[Airline] = {'Qantas'} >}[Cost PP]), '£#,##0', '.' , ',' )

Re: Multiple Criteria in one formula (similar to a SUMIFS statement)??

Wed, 24 Apr 2013 11:33:11 GMT

Please take attention to Set analysis. The straight answer to your question

=max ({$<[PriceTypeDescription] = {'Single'}, [Ticket Category] = {'First Class'}, [Airline] = {'Qantas'}>} [Cost PP])

Re: Multiple Criteria in one formula (similar to a SUMIFS statement)??

Wed, 24 Apr 2013 11:33:37 GMT

Please take attention to Set analysis. The straight answer to your question

=max ({$<[PriceTypeDescription] = {'Single'}, [Ticket Category] = {'First Class'}, [Airline] = {'Qantas'}>} [Cost PP])

Re: Multiple Criteria in one formula (similar to a SUMIFS statement)??

sushil353 — Wed, 24 Apr 2013 11:57:40 GMT

Try this:

='Single Selling Price : ' & num(max ({< [PriceTypeDescription] = {'Single'},[Ticket Category] = {'First Class'},[Airline] = {'Qantas'}>} [Cost PP]), '£#,##0', '.' , ',' )

HTH

Sushil

Re: Multiple Criteria in one formula (similar to a SUMIFS statement)??

sushil353 — Wed, 24 Apr 2013 11:57:57 GMT

Try this:

='Single Selling Price : ' & num(max ({< [PriceTypeDescription] = {'Single'},[Ticket Category] = {'First Class'},[Airline] = {'Qantas'}>} [Cost PP]), '£#,##0', '.' , ',' )

HTH

Sushil

Re: Multiple Criteria in one formula (similar to a SUMIFS statement)??

jagan — Wed, 24 Apr 2013 12:01:24 GMT

Hi,

Try this

= Num(Max ({<[PriceTypeDescription] = {'Single'}, [Ticket Category] = {'First Class'}, [Ticket Category] = {'First Class'}, [Airline] = {'Qantas'} > }[Cost PP]), '£#,##0', '.' , ',' )

Hope this helps you.

Regards,

Jagan.