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)
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
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, not used Makearray before, knew what it did just never really had a use. I did have a go at a dynamic version before watching , made the mistake of trying to put a let within Lambda, which mean it was trying to refer to something 'outside' , got there in the end.
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.
Amazing Mark!! Loved your solution. I tried to get this done with nested reduce and Scan but i found Makearray very powerful. I had to use double reduce to iterate through Item and then region but it was fun. Keep bringing these vedios. Thanks a lot. =DROP(REDUCE("",UNIQUE(F6:F21),LAMBDA(x,y,VSTACK(x, LET(f,FILTER(G6:H21,F6:F21=y), r,DROP(REDUCE("",UNIQUE(G6:G21),LAMBDA(a,v,HSTACK(a, SUM(FILTER(TAKE(f,,-1),TAKE(f,,1)=v,0))))),,1), I,IFNA(r,""),SCAN(0,I,LAMBDA(x,y,x+y)))))),1)
That’s a bit of a brain melting function. I wonder if it’s possible with a single DROP/REDUCE/LAMBDA/VSTACK. I haven’t tried, but logically, it should be.
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
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.
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.
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. ❤️❤️
Nice use of MAKEARRAY here. MMULT can also accomplish it in one go using the same logic... =MMULT(--(TOROW(MATCH(Data[Item],F11#,0))=SEQUENCE(ROWS(F11#))),(MATCH(Data[Region],G10#,0)
And if MMULT gives you too much grief, GROUPBY can also get the job done by broadcasting the values across for each applicable region: =GROUPBY(Data[Item],(MATCH(Data[Region],G10#,0)
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.
Just a quick question. Wouldn’t this be a better solution? =LET(rowNames,K5#,colNames,L4#,rowPos,MATCH(Data[Flavor],rowNames, 0),colPos,MATCH(Data[Week],colNames, 0),result,MAKEARRAY(ROWS(rowNames),COLUMNS(colNames),LAMBDA(r,c,INDEX(FILTER(Data[Qty. Sold],(rowPos=r)*(colPos=c),0),1))),result)
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.
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. 👍
Brilliant and creative as always 👍. Thanks Mark for sharing your tips which are all gems ❤
My pleasure, thanks Kebin.
I'm a BIG fan of the Let function. I'd love to see a video on just that, and all the many ways it can be helpful. (and its faults too)
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. 👍
Excellent Mark!
Thanks, glad you liked it!
Thanks, not used Makearray before, knew what it did just never really had a use.
I did have a go at a dynamic version before watching , made the mistake of trying to put a let within Lambda, which mean it was trying to refer to something 'outside' , got there in the end.
I’m glad you got a solution. These new DA functions do take a bit of practice.
Nice thanks Mark
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
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.
Amazing Mark!! Loved your solution. I tried to get this done with nested reduce and Scan but i found Makearray very powerful. I had to use double reduce to iterate through Item and then region but it was fun. Keep bringing these vedios. Thanks a lot.
=DROP(REDUCE("",UNIQUE(F6:F21),LAMBDA(x,y,VSTACK(x,
LET(f,FILTER(G6:H21,F6:F21=y),
r,DROP(REDUCE("",UNIQUE(G6:G21),LAMBDA(a,v,HSTACK(a,
SUM(FILTER(TAKE(f,,-1),TAKE(f,,1)=v,0))))),,1),
I,IFNA(r,""),SCAN(0,I,LAMBDA(x,y,x+y)))))),1)
That’s a bit of a brain melting function. I wonder if it’s possible with a single DROP/REDUCE/LAMBDA/VSTACK. I haven’t tried, but logically, it should be.
Loved It 👌🏽
Thank You!
Glad you liked it! 😁
As usual, very very informative video(s).Thank you, Sir🙏 you are my guiding star.
Awesome! Definitely I'm gonna try it!
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 👍
I need vedio by this technique@@ExcelOffTheGrid
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.❤️
Maybe not the easiest syntax, but explained brilliantly!
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 :)
you are good.
Thanks for watching.
Hello @Marc, can this formula works when rhe data set is in a range instead of a table?
Yes, can work with standard ranges too.
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
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.
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.
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.
❤️❤️
Thanks, I appreciate that. 🪄
Nice use of MAKEARRAY here. MMULT can also accomplish it in one go using the same logic...
=MMULT(--(TOROW(MATCH(Data[Item],F11#,0))=SEQUENCE(ROWS(F11#))),(MATCH(Data[Region],G10#,0)
And if MMULT gives you too much grief, GROUPBY can also get the job done by broadcasting the values across for each applicable region:
=GROUPBY(Data[Item],(MATCH(Data[Region],G10#,0)
Hello Mark, why you have written "
If we want a running total it needs to sum the previous quarters too.
For Q3:
- if we use “=“, it only gives Q3
- if we use “
@@ExcelOffTheGrid Thank you
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.
Just a quick question. Wouldn’t this be a better solution?
=LET(rowNames,K5#,colNames,L4#,rowPos,MATCH(Data[Flavor],rowNames, 0),colPos,MATCH(Data[Week],colNames, 0),result,MAKEARRAY(ROWS(rowNames),COLUMNS(colNames),LAMBDA(r,c,INDEX(FILTER(Data[Qty. Sold],(rowPos=r)*(colPos=c),0),1))),result)
How come you are getting unique formula as is in table format it won't works for me 😮😮
Thats my luck to see this video ❤️