Perfect! Generator MAKEARRAY is a very, very useful function... With dynamic arrays, we can simply SUM using (if/where) criteria, thus syntactically removing FILTER, eg =MAKEARRAY(COUNTA(F10#),COUNTA(G9#), LAMBDA(r,c, SUM( Data4[Value] * (r>=XMATCH(Data4[Item],F10#)) * (c=XMATCH(Data4[Region],G9#)) ) )) And... I call this implementation "Running total by row" :) , ie column is fixed in the formula
Great video !! Great solution !! For fun , we can change the order, in general, adding index rules and still use native GROUPBY and/or PIVOTBY results. (remove rules after if we want) Also for running totals good old MMULT can do the trick. step 1. extract inner array of the pivotby function; step 2: running tot MMULT(array, seq(4)
Beautiful, simply genius. For anyone attempting to try and getting confused by ranges, use following formula with tabular ranges: =LET(p,PIVOTBY(Data[Item],XMATCH(Data[Region],UNIQUE(Data[Region]))&Data[Region],Data[Value],SUM,,0,,0), c,COUNTA(UNIQUE(Data[Region])), MMULT(DROP(IFERROR(--p,0),1,1),--(SEQUENCE(c)
This is a beautiful construction, and very well explained, but I'm struggling to see why it'd be worthwhile to do this rather than a pivot table that could do the same thing...
The calculation engine of standard PivotTables is too limited. Therefore, it's not long before people resort to copying/pasting interim results out of the PivotTables and then using formulas on those interim results. Which just creates unnecessary manual actions. So I avoid standard PivotTables as much as humanly possible. Sorry, I'm not a fan.
Just a quick thought, we could use CHOOSECOLS and CHOOSEROWS with the r and c parameters of make array to get the row names and columns names to be used inside the filter criteria . Would shorten the syntax by quite a bit
Great solution, Mark, for a tough problem. I think your solution is quite efficient. Yet, I respectfully disagree with the premise: those regional headers should not be used for cumulative sums because those results are misleading for the consumer of the report. But of course, this was only an example to prove a point. Again, well done. 👍
Agreed, it would be an odd request to have regions in the columns. North, South, East, West are just easy to demonstrate the concepts with. However, having a running total by a non-alphabetical category is certainly a real scenario.
Excellent work, Mark. Absolutely logical in the process. Thank you very much for sharing these cases, as they respond to real situations in the company.
Another great video. Question on the lambda function: in this formula you define r & c in the first part of the lambda functions, and then reference the r & c later in the function, but how does lambda “know” that r is 3, and c is 4?
When using MAKEARRAY, it loops through the rows and columns. r = 1, c = 1 r = 1, c = 2 r = 1, c = 3 r = 1, c = 4 r = 2, c = 1 r = 2, c = 2 r = 2, c = 3 r = 2, c = 4 etc... Therefore, any LAMBDA inside MAKEARRAY must have 2 variables to accept the row and column values coming from MAKEARRAY. If there are 3 rows and 4 columns, the LAMBDA calculates 12 times (4 x 3) and returns each value to display in the array. It's the same with BYROW, it passes across 1 row. Therefore, the LAMBDA inside BYROW must have 1 variable declared.
The majority of people say there is no such thing as witchcraft. Watching this and I’m not so sure.🤨 Some serious spell casting going on there.🤪😂 It’s good magic though, and even if you say it’s not really magic, just really good science, it’s very interesting either way. ❤️❤️
I don't think PIVOTBY has access to values outside of it's own filter context. So we could get the totals for North, South, East and West individually, but I don't think we can get the running total, as that would require access to the data for the other categories. BTW - I would love to be proved wrong; as I couldn't think of a way to do it with PIVOTBY, but there may be one.
I don't think PIVOTBY has access to values outside of it's own filter context. So we could get the totals for North, South, East and West individually, but I don't think we can get the running total, as that would require access to the data for the other categories. BTW - I would love to be proved wrong; as I couldn't think of a way to do it with PIVOTBY, but there may be one.
Perfect! Generator MAKEARRAY is a very, very useful function...
With dynamic arrays, we can simply SUM using (if/where) criteria, thus syntactically removing FILTER, eg
=MAKEARRAY(COUNTA(F10#),COUNTA(G9#),
LAMBDA(r,c,
SUM( Data4[Value] * (r>=XMATCH(Data4[Item],F10#)) * (c=XMATCH(Data4[Region],G9#)) )
))
And... I call this implementation "Running total by row" :) , ie column is fixed in the formula
Ah, Yes! Very nice. Great stuff. Thanks for sharing. 👍
Great video !! Great solution !!
For fun , we can change the order, in general, adding index rules and still use native GROUPBY and/or PIVOTBY results. (remove rules after if we want)
Also for running totals good old MMULT can do the trick.
step 1. extract inner array of the pivotby function; step 2: running tot MMULT(array, seq(4)
WOW! 🤯 - that is some next level logic. Amazing!
Beautiful, simply genius. For anyone attempting to try and getting confused by ranges, use following formula with tabular ranges:
=LET(p,PIVOTBY(Data[Item],XMATCH(Data[Region],UNIQUE(Data[Region]))&Data[Region],Data[Value],SUM,,0,,0),
c,COUNTA(UNIQUE(Data[Region])),
MMULT(DROP(IFERROR(--p,0),1,1),--(SEQUENCE(c)
@@muhammadasad729 Thank you!! 😊✌
Excellent demonstration of building up a solution and then refining the implementation for ease of understanding and reuse.
Thank you. I'm glad it all made sense by the end.
This is a beautiful construction, and very well explained, but I'm struggling to see why it'd be worthwhile to do this rather than a pivot table that could do the same thing...
The calculation engine of standard PivotTables is too limited. Therefore, it's not long before people resort to copying/pasting interim results out of the PivotTables and then using formulas on those interim results. Which just creates unnecessary manual actions.
So I avoid standard PivotTables as much as humanly possible. Sorry, I'm not a fan.
Brilliant and creative as always 👍. Thanks Mark for sharing your tips which are all gems ❤
My pleasure, thanks Kebin.
Just a quick thought, we could use CHOOSECOLS and CHOOSEROWS with the r and c parameters of make array to get the row names and columns names to be used inside the filter criteria . Would shorten the syntax by quite a bit
I like it. I need to test it, but I like it 👍
Great solution, Mark, for a tough problem. I think your solution is quite efficient.
Yet, I respectfully disagree with the premise: those regional headers should not be used for cumulative sums because those results are misleading for the consumer of the report.
But of course, this was only an example to prove a point. Again, well done. 👍
Agreed, it would be an odd request to have regions in the columns. North, South, East, West are just easy to demonstrate the concepts with. However, having a running total by a non-alphabetical category is certainly a real scenario.
This looks something super advanced and i dunno if i use it or not in my works but if i have to, thanks in advance for such solution
Excellent work, Mark. Absolutely logical in the process. Thank you very much for sharing these cases, as they respond to real situations in the company.
Thanks - I'm glad you like it. 👍
Dude, you have a beautiful mind.
My mind is normally quite chaotic, but occasionally something bubbles to the surface that might make sense 🤣
I second that.❤️
Loved It 👌🏽
Thank You!
Brilliant and please share more content with filter() function magic.
I've got 3 or 4 videos about FILTER on the channel - go check them out. 😁
@@ExcelOffTheGrid Filter magic is never enough :)
Excellent Mark!
Thanks, glad you liked it!
Another great video.
Question on the lambda function: in this formula you define r & c in the first part of the lambda functions, and then reference the r & c later in the function, but how does lambda “know” that r is 3, and c is 4?
When using MAKEARRAY, it loops through the rows and columns.
r = 1, c = 1
r = 1, c = 2
r = 1, c = 3
r = 1, c = 4
r = 2, c = 1
r = 2, c = 2
r = 2, c = 3
r = 2, c = 4
etc...
Therefore, any LAMBDA inside MAKEARRAY must have 2 variables to accept the row and column values coming from MAKEARRAY.
If there are 3 rows and 4 columns, the LAMBDA calculates 12 times (4 x 3) and returns each value to display in the array.
It's the same with BYROW, it passes across 1 row. Therefore, the LAMBDA inside BYROW must have 1 variable declared.
@@ExcelOffTheGrid thank you, that’s a great explanation.
Mark, thanks for sharing the excellent solution. I learnt a lot from you. I have couple of questions:
a. During the videio, you put "
We need
The majority of people say there is no such thing as witchcraft.
Watching this and I’m not so sure.🤨
Some serious spell casting going on there.🤪😂
It’s good magic though, and even if you say it’s not really magic, just really good science, it’s very interesting either way.
❤️❤️
Pivotby?
I don't think PIVOTBY has access to values outside of it's own filter context. So we could get the totals for North, South, East and West individually, but I don't think we can get the running total, as that would require access to the data for the other categories.
BTW - I would love to be proved wrong; as I couldn't think of a way to do it with PIVOTBY, but there may be one.
pivotby?
I don't think PIVOTBY has access to values outside of it's own filter context. So we could get the totals for North, South, East and West individually, but I don't think we can get the running total, as that would require access to the data for the other categories.
BTW - I would love to be proved wrong; as I couldn't think of a way to do it with PIVOTBY, but there may be one.