topic Re: Sum of a measure based on Max of another measure of current month in App Development

Sum of a measure based on Max of another measure of current month

Braveen — Wed, 06 Mar 2024 09:58:00 GMT

Hi,

I have a data that looks like below (simplified).

I need sum of current month plan based on the maximum plan revision. I have used this expression.

{<Plan month = {"$(=Max(Plan month))"}, Plan Rev = {"$(=Max(Plan Rev))"} >} Sum(Plan).

It works fine when I choose a month or works if all the months have 2 plan revisions. I would like this Max(Plan Rev) should take the current month in to account and display the max revision of that month.

Thanks,

Braveen

Re: Sum of a measure based on Max of another measure of current month

maxgro — Wed, 06 Mar 2024 10:14:55 GMT

try with [Plan month] as dimension and this expression

FirstSortedValue( Plan, -[Plan Rev])

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/BasicAggregationFunctions/firstsortedvalue.htm

Re: Sum of a measure based on Max of another measure of current month

Clement15 — Wed, 06 Mar 2024 10:20:43 GMT

Hello, this formula should work

{<month = {"$(=Max(month))"}, Rev = {"$(=Max({<month = {"$(=Max(month))"}>}Rev))"} >} Sum(Plan)

Re: Sum of a measure based on Max of another measure of current month

Braveen — Wed, 06 Mar 2024 10:24:02 GMT

I am sorry @maxgro I didn't understand your suggestion. Could you please elaborate a little?

where do you suggest I use the FirstSortedValue? Since, it returns dimensions as output (I looked at the examples off the link you shared), I am not sure how it helps.

Re: Sum of a measure based on Max of another measure of current month

Braveen — Wed, 06 Mar 2024 10:36:21 GMT

This is working as expected. Thanks @Clement15