topic Re: Understanding of set expression combined with aggregated function in App Development

Understanding of set expression combined with aggregated function

Applicable88 — Thu, 21 May 2020 13:13:16 GMT

Hello!

I hope all is good!

I have an example which is hard for me to grasp. For instance I have the following table:

Company	Year	Item	Sale
A	2014	Smartphone	6000
A	2018	Tools	4000
A	2019	Haircut	7000
B	2017	Bolts	500
B	2018	Pen	400
B	2019	Smartphone	400
C	2019	Tools	800
C	2020	Haircut	20
C	2019	Harddrive	300

To have the sum(sale) is a no brainer. If I want the top selling product for each company it is also understandable:

=Max ( aggr ( sum ( Sale ), Company , Item) )

But when it comes to a added set expression to always return me the top selling product for the PREVIOUS YEAR I implemented a "set expression":

=Max({<Year={$(=Max(Year)-1)}>} aggr (sum( {<Year={$(=Max(Year)-1)}>} Sale),Company,Item))

As you can see, the set expression were used twice, once for MAX function and the other time for AGGR. And that is where I get my numbers, I just don't understand why. Why I need the (Max Year-1 ) twice?

For me the following makes more sense , but it simply wouldn't return what I want: = Max( aggr(sum( {<Year={$(=Max(Year)-1)}>} Sale), Company , Item) )

The last one would be simply a variation of "=Max ( aggr ( sum ( Sale ), Company , Item) )" with an added "give me the last year values " command. Only the long one with two set expression works out.

I hope someone can explain to me in plain words. And also the differences.

Stay safe. Thanks in advance!

Re: Understanding of set expression combined with aggregated function

sunny_talwar — Thu, 21 May 2020 13:25:15 GMT

So, to make sure you understand, I want to start by telling that this will work too if you don't select a single value in year field.

=Max(Aggr (sum( {<Year={$(=Max(Year)-1)}>} Sale),Company,Item))

and here is why

1) Nothing is selected in Year and assuming max year in the data is 2020

Set Analysis for Max	Set Analysis for Sum	Intersection of the two sets
All Years	2019	2019

2) Selected 2020 (also the max year)

Set Analysis for Max	Set Analysis for Sum	Intersection of the two sets
2020	2019	No intersection

Now changing the expression to this

=Max({<Year={$(=Max(Year)-1)}>} Aggr (sum( {<Year={$(=Max(Year)-1)}>} Sale),Company,Item))

1) Nothing is selected in Year and assuming max year in the data is 2020

Set Analysis for Max	Set Analysis for Sum	Intersection of the two sets
2019	2019	2019

2) Selected 2020 (also the max year)

Set Analysis for Max	Set Analysis for Sum	Intersection of the two sets
2019	2019	2019

You can also try this

=Max({<Year>} Aggr (sum( {<Year={$(=Max(Year)-1)}>} Sale),Company,Item))

1) Nothing is selected in Year and assuming max year in the data is 2020

Set Analysis for Max	Set Analysis for Sum	Intersection of the two sets
All Years	2019	2019

2) Selected 2020 (also the max year)

Set Analysis for Max	Set Analysis for Sum	Intersection of the two sets
All Years	2019	2019

Does it make sense?

Re: Understanding of set expression combined with aggregated function

Applicable88 — Fri, 22 May 2020 12:02:12 GMT

Hey Sunny_Talwar!

as always real MVP :).

I still had to read it over and over, but now I got it. Thank you so much for providing me with all the infos and alternatives. It really helped me with my understanding. Many will benefit from your function examples.

Have a great day buddy and stay safe!