7 Replies Latest reply: Apr 24, 2013 11:24 AM by Yusuf Ali

# Return values "where" condition is true

Hi,

I am trying to get Single Prices and Twin Prices into seperate boxes. Very simple. Just a box that shows the amount.

So, at the minute I have these:

='Single Selling Price : ' & num(max ([Cost PP]), '£#,##0', '.' , ',' )

I have another table [PriceTypeDescription] that shows me the price for a single and the price for a twin. I was wondering how I can do a statement in my above formula that sort of says:

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

Chris

• ###### Re: Return values "where" condition is true

Hi ,

Try the below code

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

//Yusuf

• ###### Re: Return values "where" condition is true

Sorry to bother again.. but 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: Return values "where" condition is true

HI ,

Yes , we can apply more than one criteria .

Try  the below code .

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

Thanks & Regards

Yusuf Ali

• ###### Re: Return values "where" condition is true

Sorry to bother again.. but 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: Return values "where" condition is true

HI ,

Yes , we can apply more than one criteria .

Try  the below code .

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

Thanks & Regards

Yusuf Ali

• ###### Re: Return values "where" condition is true

Sorry to bother again.. but 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: Return values "where" condition is true

HI ,

Yes , we can apply more than one criteria .

Try  the below code .

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

Thanks & Regards

Yusuf Ali