Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
fantong3
New Contributor

Max function with limited selection

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!

1 Solution

Accepted Solutions
asinha1991
Contributor III

Re: Max function with limited selection

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

View solution in original post

5 Replies
Partner
Partner

Re: Max function with limited selection

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
Contributor III

Re: Max function with limited selection

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
New Contributor

Re: Max function with limited selection

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
New Contributor

Re: Max function with limited selection

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
Contributor III

Re: Max function with limited selection

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

View solution in original post