Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Max( {1<Date=p(Date), currencyPair =p(currencyPair )>}total < currencyPair,Date> rate)* count(distinct client) and hide 0...there could be better with aggr
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))
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)
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!
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!
Max( {1<Date=p(Date), currencyPair =p(currencyPair )>}total < currencyPair,Date> rate)* count(distinct client) and hide 0...there could be better with aggr