×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Pivot Table - Separate columns for Actual and Forecast Value

Pivot Table - Separate columns for Actual and Forecast Value

Pivot Table - Separate columns for Actual and Forecast Value

(OP)
Hello Everyone,

My first post to this group so please delete if not related.

I’m working on some metrics and would like to know if someone here was able to create separate columns for actual and forecast values in pivot table. See attached image.


Thank you in advance!

RE: Pivot Table - Separate columns for Actual and Forecast Value

Hi,

Do not understand your question. This looks like you just typed some data into three columns. A PivotTable needs a data source in order to generate a result or even suggest what kind of result could be generated.

Please post a fair representation of your source data that can be copied and pasted into an Excel sheet, showing the relevant fields.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Pivot Table - Separate columns for Actual and Forecast Value

Well I thought about this.

First off, WOW! Are you also under budget? Sounds like Trump: ahead of schedule and under budget! winky smile

I'm guessing that your source data has columns like this...


For any task you have a Forecast Date and an Actual Date for which there may be a date or not.

Since Actual & Forecast can have mutually exclusive MonthYear data, I don't know how you'd get all the MonthYear data into the Pivot Date column and therefore get the correct counts.

So I made my own pivot table using formulae in my first solution and a pivot query as a second solution.

My pivot query results...
date	Actual	Forecast
2020-02		1
2020-03	1	1
2020-04		2
2020-05	2	1
2020-07	1	
2021-01	1	
 

My sql...

CODE

transform    count(*)
select format(a.dat, 'yyyy-mm') as [date]
from 
(
SELECT `Sheet1$`.Actual as [dat], 'Actual' as [cat]
FROM `C:\Users\Owner\iCloudDrive\et-PivotTable.xlsx`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Actual<>0)
union 
SELECT `Sheet1$`.Forecast, 'Forecast' 
FROM `C:\Users\Owner\iCloudDrive\et-PivotTable.xlsx`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Forecast<>0)
) a
group by  format(a.dat, 'yyyy-mm')
pivot    a.cat 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Pivot Table - Separate columns for Actual and Forecast Value

(OP)
Wow, thank you @SkipVought!! Appreciate your effort Mate, awesome!

I think my questions is to broad, apologies. I have attached snapshot of what I'm trying to achieve using Pivot table or Power pivot.
Here I got the source data and the desired pivot table output. Note, I know power query, power pivot and little knowledge about DAX formula / measures.

Basically I just want separate columns for actual dates (<=reporting month) and forecast dates (>reporting month). So as the cumulative.

Hope this make sense now, if still not clear happy to elaborate further. Just want to automate this! Thank you so much!

Mac

RE: Pivot Table - Separate columns for Actual and Forecast Value

Here's another shot at your requirements. Workbook attached.

The YELLOW cells indicate cells where the user can enter data to manipulate the pivot. I think I should have put the Reporting Month value on the pivot sheet. I would also add in the Factors sheet a list of dates to use to select Reporting Month in a Data Validation Drop Down list. I'd generate that via a query, as I also would generate a list of Release Codes via a query to make the whole thing more automated.

BTW, a suggestion when you use MS Query in Excel. In order to make the workbook portable, you need a means of changing the connection string to conform with the current workbook's Path and Name, which can be coded...

CODE

Dim sPath As String, sDB As String
sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name 
...and substitute these variables in the Connection property and in the FROM statement in the SQL code like this...

CODE

sSQL = "SELECT `Sheet1$`.Actual as [dat], 'Actual' as [cat] "
sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Sheet1$` `Sheet1$` "
sSQL = sSQL & " WHERE (`Sheet1$`.Actual<>0)" 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close