Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
QS_kdefok
Partner
Partner

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 :

PROJECTDATEVALUE
A10/10/201920
A20/10/201910
A10/11/201913
A20/11/201910
B11/10/20195
B21/10/20193
B11/11/20196
B21/11/20194
C12/10/20198
C22/10/20196
C12/11/20192
C22/11/20191

 

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 :

image.png

 

 

 

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 (6)
1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

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

 

Lorenzo

View solution in original post

6 Replies
QS_kdefok
Partner
Partner
Author

Here the QVF file of my above topic.

lorenzoconforti
Specialist II
Specialist II

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

 

Lorenzo

View solution in original post

QS_kdefok
Partner
Partner
Author

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

Happy festive season 😊

QS_kdefok
Partner
Partner
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

lorenzoconforti
Specialist II
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])) 

QS_kdefok
Partner
Partner
Author

Tested and perfecly works, thank you so much !💪

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

Merry Xmas eve. 🤶