Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
QS_kdefok
Partner - Contributor III
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 :

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 (5)
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 - Contributor III
Partner - Contributor III
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

QS_kdefok
Partner - Contributor III
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 😊

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

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 - Contributor III
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. 🤶