topic Re: Max function with limited selection in App Development

Max function with limited selection

fantong3 — Tue, 17 Sep 2019 07:02:31 GMT

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!

Re: Max function with limited selection

StarinieriG — Tue, 17 Sep 2019 07:09:56 GMT

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))

Re: Max function with limited selection

asinha1991 — Tue, 17 Sep 2019 07:40:43 GMT

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)

Re: Max function with limited selection

fantong3 — Wed, 18 Sep 2019 02:44:02 GMT

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!

Re: Max function with limited selection

fantong3 — Wed, 18 Sep 2019 02:51:38 GMT

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!

Re: Max function with limited selection

asinha1991 — Wed, 18 Sep 2019 08:00:14 GMT

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