# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$400 before Dec 31st: REGISTER NOW!
cancel
Showing results for
Did you mean:
Partner - Contributor III

## MAX on intricated aggregations with SUM of MIN

Hello QS community,

I need your help to make me sleeping better, once this behind me.

I have this data set :

 PROJECT DATE VALUE A 10/10/2019 20 A 20/10/2019 10 A 10/11/2019 13 A 20/11/2019 10 B 11/10/2019 5 B 21/10/2019 3 B 11/11/2019 6 B 21/11/2019 4 C 12/10/2019 8 C 22/10/2019 6 C 12/11/2019 2 C 22/11/2019 1

At different date, we get a value for the project.

My purpose is to get the minimum of each project per month, and sum them.

With the formula "=sum(aggr(Min(VALUE),PROJECT, [DATE.autoCalendar.YearMonth]))" I have this correct table :

Now I would like to get the max of this, so my formula should be :   "=MAX(sum(aggr(Min(VALUE),PROJECT, [DATE.autoCalendar.YearMonth])))

And I would get 19 as result in my example. But this formula doesn 't work.

What is the mistake ?

I attach my qvf file as simple.

Many thanks by advance for your support 🙂

Labels (5)

• ### Set Analysis

1 Solution

Accepted Solutions
Specialist II

=max(aggr(sum(aggr(Min(VALUE),PROJECT, [DATE.autoCalendar.YearMonth])), [DATE.autoCalendar.YearMonth]))

Lorenzo

6 Replies
Partner - Contributor III
Author

Here the QVF file of my above topic.

Specialist II

=max(aggr(sum(aggr(Min(VALUE),PROJECT, [DATE.autoCalendar.YearMonth])), [DATE.autoCalendar.YearMonth]))

Lorenzo

Partner - Contributor III
Author

That perfectly works ! I was a little bit lost in my aggregation and need to revise the aggregation chapter.

Happy festive season 😊

Partner - Contributor III
Author

Hello Lorenzo,

Thanks for your answer yesterday on this topic : MAX on intricated aggregations with SUM of MIN

I need one more time your help, still on the same example, I would like to get the value for the latest month. (in the past sample, get 15 as 2019-Nov is my last month)

I thought to use this formula : "=FirstSortedValue(TOTAL aggr(sum(aggr(Min(ncloc),FULL_NAME, [DATE.autoCalendar.YearMonth])), -[DATE.autoCalendar.YearMonth]))"

But it doesn't work, and make me stuck.

Thanks by advance again.

Regards,

Ken Defossez

Specialist II

Try with this:

=sum(aggr({<[DATE.autoCalendar.YearMonth]={"=num([DATE.autoCalendar.YearMonth])=\$(=max(aggr(nodistinct num([DATE.autoCalendar.YearMonth]),[DATE.autoCalendar.YearMonth])))"}>} Min(VALUE),PROJECT, [DATE.autoCalendar.YearMonth]))

Partner - Contributor III
Author

Tested and perfecly works, thank you so much !💪

I'll work on it to understand and improve in QS.

Merry Xmas eve. 🤶

Tags
Community Browser