Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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?
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!