Rekha viewed episode 655 where I used Group Field in a pivot table to create a year-over-year report. But Rekha needs current month from last year, current month from this year, variance, then YTD through current month from last year, YTD this year, variance. Urgently. In Excel 2010.
While this would be a perfect report for Power Pivot, Rekha hasn't come up the Power Pivot learning curve yet. And we need the report urgently.
I am calling this an impossible pivot table because once you group daily dates to months and years, you are not allowed to add a calculated item inside the pivot table to show a variance. Many ideas flashed through my mind: Run the data through PowerPivot and used Named Sets. Make two pivot tables side by side, one showing monthly and one showing YTD. Abandon pivot tables and use SUMIFS instead. I ended up using the GetPivotData technique, although there are probably dozens of ways to approach this impossible problem.
While this would be a perfect report for Power Pivot, Rekha hasn't come up the Power Pivot learning curve yet. And we need the report urgently.
I am calling this an impossible pivot table because once you group daily dates to months and years, you are not allowed to add a calculated item inside the pivot table to show a variance. Many ideas flashed through my mind: Run the data through PowerPivot and used Named Sets. Make two pivot tables side by side, one showing monthly and one showing YTD. Abandon pivot tables and use SUMIFS instead. I ended up using the GetPivotData technique, although there are probably dozens of ways to approach this impossible problem.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn excel from MrExcel podcast. Episode # 1873 Using GetPivotData for impossible Pivot Table Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in by Rekha at YouTube Watched episode 865 that shows how to create a pivot table with year over year.
but Rekha needs to create an impossible pivot table with this month from last year, this month from this year, the variance and then year-to-date for last year, year-to-date for last this year and then the variance.
Urgently Excel 2010, please.
Alright. well now if Rekha had PowerPivot and had time to study Rob's book, I'm using DAX formulas you could do this with PowerPivot.
But I'm going to assume that there is no PowerPivot available right now.
By the way PowerPivot is absolutely free in Excel 2010.
I would absolutely solve this problem, but if you have to do something urgently that is you're not going to have time for that alright, so.
Here's what I did. I created a Shell report with customer and these fields we want over here a parameter with Report Month that right now has 2. I'm going to name that parameter. I'm gonna call it RptMo RptMo that has to be a single word.
No spaces up there so we have RptMo.
Back to the original data, we're going to add one new column here called YTDRev and we're going to say =IF the MONTH of this date is less than or equal to our RptMo, then we want the revenue otherwise we want the 0.
Double click to copy that down.
Alright now create a pivot table.
Just a regular pivot table not a PowerPivot table from this data. Click OK Down the side let's put customer across the top. Will put Dates and Revenue here in the Values area.
Now we're going to take these daily dates, and we're going to Group Field and group up to Months and Years like that. Let's see Design Report Layout Show in Tabular Form. So we have true headings up there.
One funny thing when we group months and days what they do not give us a year total and the year total for year to date is what we actually need.
So making sure that you're on a year 2012. Come back to Field Settings change that to Automatic and that gives us our 2012 total. All right, so I had a another field here, so we have Revenue and YTDRev. This is admittedly an ugly pivot table, and there is no good way to get exactly what Rekha needs.
So we're going to switch over to using GETPIVOTDATA.
Normally most people hate GETPIVOTDATA. If you've turned it off in the past, it will be unchecked there. You want to make sure that that is check marked.
We're going to come over here to take a look at our sheets so our customers down the side.
That's just going to be a straight copy and paste. So Ctrl + C And Alt +E + S+ V there Okay. Now to build this report the first two formulas are going to be formulas that we build by pointing into the pivot table. So here I'm looking for AIG February 2012, so I type the = sign go click on the pivot table sheet, find AIG, and we're looking for February Sum of Revenue right there, that's an empty cell in this particular case, but that's okay.
We have to click on the right cell in order to get our GETPIVOTDATA formula and what we see here is that they've hard-coded the fact this is month 2, they've hard-coded AIG.
They've hard-coded year. All right, so we're going to need to change that hard coding to point to our RptMo field for AIG we're going to delete that and click on AIG over in A5.
I always want that to point to column A so I'm going to press F4 1 2 3 times to put the $ before the A and for 2012 I'm going to click on cell B3 and there I want it to lock it down to the row so F4 twice to do that. Alright so we can now copy that formula throughout the pivot table alright, and we're actually getting February 2012 February 2013 at the bottom. Alt + = to put the grand total in the variance. Rekha didn't say if it was a variance percentage or a absolute variance. If it was just an absolute variance then 2013 - 2012 and copy that down not as a percentage. I'm assuming it's a percentage though, so 2013/2012 - 1 gives us a div by error so we do IFERROR and we show a 0 when there were no sales and copy that down. Alright so that formula copies over to G.
Now here for the year date. What we have to do is we have to add up the yearly number for all of 2012 so again an equal sign here.
Go back to our pivot table, and we are going to find the 2012 Sum of YTDReve.
Again click on the right cell and come back and examine the GetPivotData formula.
We have to change the AIG hard-coding to point to cell A5 and years to point to E3 and again F4 2 times there should be able to copy this throughout and again at the bottom Alt + = to put the grand totals in.
All right so we now have the original data. We added one column there created the world's worst pivot table here.
No one will ever have to see this and then a report that is pulling data from the pivot table to get those columns that we're looking for, for that report. Again this would have been possible using the free PowerPivot add-in but you'd have to use name sets and be able to create a couple of Dax measures using time intelligence functions, and that would have involved a calendar table and it sounds like this report has to be done this morning.
So I'm going with these convoluted set of steps at least you haven't done in 10.
Well hey I want to thank Rekha for sending that question in.
I want to thank you for stopping by. Will see you next time for another net cast from MrExcel.
Learn excel from MrExcel podcast. Episode # 1873 Using GetPivotData for impossible Pivot Table Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in by Rekha at YouTube Watched episode 865 that shows how to create a pivot table with year over year.
but Rekha needs to create an impossible pivot table with this month from last year, this month from this year, the variance and then year-to-date for last year, year-to-date for last this year and then the variance.
Urgently Excel 2010, please.
Alright. well now if Rekha had PowerPivot and had time to study Rob's book, I'm using DAX formulas you could do this with PowerPivot.
But I'm going to assume that there is no PowerPivot available right now.
By the way PowerPivot is absolutely free in Excel 2010.
I would absolutely solve this problem, but if you have to do something urgently that is you're not going to have time for that alright, so.
Here's what I did. I created a Shell report with customer and these fields we want over here a parameter with Report Month that right now has 2. I'm going to name that parameter. I'm gonna call it RptMo RptMo that has to be a single word.
No spaces up there so we have RptMo.
Back to the original data, we're going to add one new column here called YTDRev and we're going to say =IF the MONTH of this date is less than or equal to our RptMo, then we want the revenue otherwise we want the 0.
Double click to copy that down.
Alright now create a pivot table.
Just a regular pivot table not a PowerPivot table from this data. Click OK Down the side let's put customer across the top. Will put Dates and Revenue here in the Values area.
Now we're going to take these daily dates, and we're going to Group Field and group up to Months and Years like that. Let's see Design Report Layout Show in Tabular Form. So we have true headings up there.
One funny thing when we group months and days what they do not give us a year total and the year total for year to date is what we actually need.
So making sure that you're on a year 2012. Come back to Field Settings change that to Automatic and that gives us our 2012 total. All right, so I had a another field here, so we have Revenue and YTDRev. This is admittedly an ugly pivot table, and there is no good way to get exactly what Rekha needs.
So we're going to switch over to using GETPIVOTDATA.
Normally most people hate GETPIVOTDATA. If you've turned it off in the past, it will be unchecked there. You want to make sure that that is check marked.
We're going to come over here to take a look at our sheets so our customers down the side.
That's just going to be a straight copy and paste. So Ctrl + C And Alt +E + S+ V there Okay. Now to build this report the first two formulas are going to be formulas that we build by pointing into the pivot table. So here I'm looking for AIG February 2012, so I type the = sign go click on the pivot table sheet, find AIG, and we're looking for February Sum of Revenue right there, that's an empty cell in this particular case, but that's okay.
We have to click on the right cell in order to get our GETPIVOTDATA formula and what we see here is that they've hard-coded the fact this is month 2, they've hard-coded AIG.
They've hard-coded year. All right, so we're going to need to change that hard coding to point to our RptMo field for AIG we're going to delete that and click on AIG over in A5.
I always want that to point to column A so I'm going to press F4 1 2 3 times to put the $ before the A and for 2012 I'm going to click on cell B3 and there I want it to lock it down to the row so F4 twice to do that. Alright so we can now copy that formula throughout the pivot table alright, and we're actually getting February 2012 February 2013 at the bottom. Alt + = to put the grand total in the variance. Rekha didn't say if it was a variance percentage or a absolute variance. If it was just an absolute variance then 2013 - 2012 and copy that down not as a percentage. I'm assuming it's a percentage though, so 2013/2012 - 1 gives us a div by error so we do IFERROR and we show a 0 when there were no sales and copy that down. Alright so that formula copies over to G.
Now here for the year date. What we have to do is we have to add up the yearly number for all of 2012 so again an equal sign here.
Go back to our pivot table, and we are going to find the 2012 Sum of YTDReve.
Again click on the right cell and come back and examine the GetPivotData formula.
We have to change the AIG hard-coding to point to cell A5 and years to point to E3 and again F4 2 times there should be able to copy this throughout and again at the bottom Alt + = to put the grand totals in.
All right so we now have the original data. We added one column there created the world's worst pivot table here.
No one will ever have to see this and then a report that is pulling data from the pivot table to get those columns that we're looking for, for that report. Again this would have been possible using the free PowerPivot add-in but you'd have to use name sets and be able to create a couple of Dax measures using time intelligence functions, and that would have involved a calendar table and it sounds like this report has to be done this morning.
So I'm going with these convoluted set of steps at least you haven't done in 10.
Well hey I want to thank Rekha for sending that question in.
I want to thank you for stopping by. Will see you next time for another net cast from MrExcel.
