Max function with limited selection

[fantong3]

Hi all,

I have a set of 2 year's data and i want to calculated a daily price range. I use max(rate)-min(rate) in a table also with other two dimension currency pair and client.  I want the max and min only reflect its daily range when i select a date but calculated a cross all client. An example will be  when i select a client, a date and a currency pair, the max() returns the max number for the day, for the currency pair but across all client. 

I tried Max( total < currencyPair > rate), which gives the correct result as long as i only select date. I want also be able filter the client. Can nay one hlep me with it?

Thanks!

[StarinieriG]

Hi,

I am not sure, but have you tried to used aggr function?Something like this

Sum(Aggr(max(rate),currencyPair,date)-Aggr(min(rate),currencyPair,date))

[asinha1991]

if you just want to take date but ignore everything else from both set and table dimension, you can do this way

Max( {1<Date=p(Date), currencyPair =p(currencyPair )>}total < currencyPair,Date> rate)

[fantong3]

Hi @asinha1991 

Thanks so much for the quick replay. This is one works great in the a table. However i am not able to select a client and display all values for this client only in the table. Is it because this function use '1' in set expression which will always display all possible values? Ideally i want to able to filter the client and only display rows for this client but the max() still calculates across all client. Thanks! 

[fantong3]

Hi @StarinieriG,

Thanks for the replay. Unfortunately this one does not return my answers.  I have tried ashiha1991's which worked but not able to show results only for a selected client. Is that possible we still showing result for selected client(in table dimension) but have max() calculate across all client? Thanks! 

[asinha1991 · Accepted solution]

Max( {1<Date=p(Date), currencyPair =p(currencyPair )>}total < currencyPair,Date> rate)* count(distinct client) and hide 0...there could be better with aggr