Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

groomskim
New Contributor

Using max function inside a sum

Hello,

I'm attempting to use a max function inside a sum that is multiplying 3 values together in a measure in Qlik Sense, but I am unable to get it to work.  Here is a sample.  The max value is a percentage.  Any help is appreciated.

SUM({<ASSET_TYPE-={'Common Stock','PE Direct Investments','Policy Loan'}>}

(Max([Pref_Pre_Tax_Cap_Chg])) * ([BOOK_VALUE] * [FX_RATE]))

Kim

1 Solution

Accepted Solutions
juraj_misina
Valued Contributor

Re: Using max function inside a sum

Hello Kimberly,

you cannot nest aggregation functions in a easy way as in your example. Based on what you need to achieve you can go with set analysis (as niclaz79‌ suggests) or use Aggr() function to nest aggregations.

If you only want to consider those values of Pref_Pre_Tax_Cap_Chg which are equal to Max(Pref_Pre_Tax_Cap_Chg), then you can go with set analysis. On the other hand, you want to consider all values and Max(Pref_Pre_Tax_Cap_Chg) depends on dimension values (rows of your table/chart), then you need to use Aggr(). Your expression might look something like this:

SUM({<ASSET_TYPE-={'Common Stock','PE Direct Investments','Policy Loan'}>} Aggr(

(Max([Pref_Pre_Tax_Cap_Chg])) * ([BOOK_VALUE] * [FX_RATE]), Dim1, Dim2))

Dim1 and Dim2 beeing your chart dimensions.

Good luck!

J

7 Replies
ahaahaaha
Honored Contributor

Re: Using max function inside a sum

Hi Kim,

It is difficult to say without seeing the initial data and the desired result. Which chart is used? Are there any dimensions? Maybe there is an opportunity to share QVF?

Regards,

Andrey

devarasu07
Honored Contributor II

Re: Using max function inside a sum

Hi,


have you tried like below?, Thanks

Max(SUM({<ASSET_TYPE-={'Common Stock','PE Direct Investments','Policy Loan'}>} [Pref_Pre_Tax_Cap_Chg] * ([BOOK_VALUE] * [FX_RATE])))

Highlighted
antoniotiman
Honored Contributor III

Re: Using max function inside a sum

Hi Kimberly,

maybe

SUM({<ASSET_TYPE-={'Common Stock','PE Direct Investments','Policy Loan'}>}

(Max(TOTAL <Dimension> [Pref_Pre_Tax_Cap_Chg])) * ([BOOK_VALUE] * [FX_RATE]))

Regards,

Antonio

niclaz79
Contributor III

Re: Using max function inside a sum

Hi, try the below where the [Pref_Pre_Tax_Cap_Chg] max is selected in the set analysis instead which should work. This only includes the lines where [Pref_Pre_Tax_Cap_Chg] is the max number. If you are looking to sum all lines with the max value of [Pref_Pre_Tax_Cap_Chg] I would have put it in a variable in the script and then used the variable for the calculation.

SUM({<ASSET_TYPE-={'Common Stock','PE Direct Investments','Policy Loan'}, [Pref_Pre_Tax_Cap_Chg] = {$(=Max([Pref_Pre_Tax_Cap_Chg]))}>} [Pref_Pre_Tax_Cap_Chg] * ([BOOK_VALUE] * [FX_RATE]))

juraj_misina
Valued Contributor

Re: Using max function inside a sum

Hello Kimberly,

you cannot nest aggregation functions in a easy way as in your example. Based on what you need to achieve you can go with set analysis (as niclaz79‌ suggests) or use Aggr() function to nest aggregations.

If you only want to consider those values of Pref_Pre_Tax_Cap_Chg which are equal to Max(Pref_Pre_Tax_Cap_Chg), then you can go with set analysis. On the other hand, you want to consider all values and Max(Pref_Pre_Tax_Cap_Chg) depends on dimension values (rows of your table/chart), then you need to use Aggr(). Your expression might look something like this:

SUM({<ASSET_TYPE-={'Common Stock','PE Direct Investments','Policy Loan'}>} Aggr(

(Max([Pref_Pre_Tax_Cap_Chg])) * ([BOOK_VALUE] * [FX_RATE]), Dim1, Dim2))

Dim1 and Dim2 beeing your chart dimensions.

Good luck!

J

groomskim
New Contributor

Re: Using max function inside a sum

Thank you for the guidance!  The Aggr function is returning what I expect.

Kim

oknotsen
Honored Contributor III

Re: Using max function inside a sum

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
Community Browser