MAX on intricated aggregations with SUM of MIN

[QS_kdefok]

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 🙂

[QS_kdefok]

Here the QVF file of my above topic.

[lorenzoconforti · Accepted solution]

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

Lorenzo

[QS_kdefok]

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

Happy festive season 😊

[QS_kdefok]

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

[lorenzoconforti]

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

[QS_kdefok]

Tested and perfecly works, thank you so much !💪

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

Merry Xmas eve. 🤶