Solved

MTD and YTD Pivot table

Posted on 2014-04-28
19
831 Views
Last Modified: 2014-05-10
I would be grateful if I can get a pivot table made out of the attached data.  I have two sheets in the attachment i.e the data and the headings of how the pivot table will look like,

Thanks in advance,
Experts-exchange.xlsx
0
Comment
Question by:Kdankwah
  • 10
  • 9
19 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
could you give a sample of data at least to understand what you mean by MDT (presume it is month to date) and YDT (Year to date) you need a place to select a certain date ??? and then these column would display the month to date and year to date ? or ... ???

It would help if you could attach the same file with some sample data showing the output of the pivot.

tks
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Furthermore in your sample dta you have several rows where you have data category and amounts however the Month and Monthid is BLANK !!! how is that ?

What is the month_id ?? just 1 for January and 2 for February ?

You do not have a date there so we can have you the Month to date.

gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
Sorry if I did not explain well.  See the new spread sheet and the sample pivot table.  All I want is to have the Month to date figure based on the Month.  

Thanks
Experts-exchange.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I don't get it. Although the sample is fine but you replaced Col B Month by Monthend which is fine

HOWEVER
the first few rows shows date 7/31/2013 ... where the fiscal year is 14 !!! and the dates are 2013 is this correct ? or they should be 2014 for fiscal year 14 and 2013 for fiscal year 13 ???

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Also what about the items that do not have a date ???

By the way would you accept a VBA solution ? as your data is not properly formatted and missing items. You also mentioned in your sample this is for a specific Segment you want to also filter the data by segment ???

so many questions.
gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
Every item will have a date.  The fiscal year runs from July of the previous year to June of the current so some date will be 2013 and others will be 2014.  

By the way, I have the pivot table already, if you can create a sumif formula to pick the monthly items for any given month that will be fine.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok so the issue is different now, pls post the pivot and I will work on the rest.
gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
Thanks for responding to my posting.  Sorry I did not respond sooner.  See the attached excel spreadsheet.  I have an explanations sheet, that tells the whole story.  Please let me know if you have any questions.  I am also open to suggestions to make this work better.

Thanks once again
2013-and-2014-for-experts-exchan.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Is this what you want ? I removed the period from general filtering and put it in column filtering this way you have each month separate.
gowflow
2013-and-2014-for-experts-exchan.xlsx
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Kdankwah
Comment Utility
Sorry, this is not what I want.  I want the Month to date column to be in front of the Year to date column as is in the template.  The way I will normally do that in the regular excel spreadsheet is this formula  Month to date:  Current minus Previous.  but I don't know how to do that in the Pivot table.  

I was thinking of using Sumifs but not have no idea that will work.  By the way, I will run the Pivot by Department.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok as not too much familiar with pivots (like you have a field that is %used) and cannot see it in your dataset how did you comeup with this field ?

Could you simply insert the MDT field in the pivot even if it is blank and I will see how to fix your issue? do it and post the new workbook.

gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
Oh I see, the %Used field is a calculated field which is created by using a formula.

Thanks
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok I have been trying to create a formula for the MDT but I am failing here is the formula
=SUMIFS([AMOUNT],[Month],"="&[Period_id],[OAD_CATEGORY],"="&[OAD_CATEGORY])

This tells it to Sum Amount if the Month column = Period_Id and Category choosen

I am not able to create a field calculated if you can based on this formula then I would like to know how.

gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
Can you put this formula in the dataset of the worksheet and see if it works?  I tried it, it runs but with large number result.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes mee too.

Question for you
What do you mean by Month to date ???

When you have these values the year to date give you a total for that year of whatever data is there, if you have beside it this Month to date and in the filtering you are asking for All periods then how you expect to have this month to date reflected ?? what do you want to see there ?

I still don't understand what you want to see in month to date. I was able to get the calculated field working but you need to tell me what is this field consist of. Please take a real example and tell me for item so and so I expect to see for 2014 year to date this amount and month to date this amount.


then and only then I can comeup with a formula.
gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
The month to date will be amount paid for the given month that you are looking at.  Normally in a excel spreadsheet you will have Current month Year to date data minus Previous month Year to date data.  Thats what month to date is.

Thanks
0
 

Author Comment

by:Kdankwah
Comment Utility
See the attached spreadsheet.  Its self explanatory.  The Month to date is always changing when you enter a new ytd.  I dont know how to do this in a Pivot table

Thanks
sample-month-to-date.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes your sample is fine however the way you have set the pivot it select data for several months unless if u click on period and select 1 month January or February in this case  year to date shows the specific month and not the year to date and this is your problem. I have been trying to explain this to you but you were not grasping it. Hope it is clearer now.
gowflow
0
 

Author Comment

by:Kdankwah
Comment Utility
Can you give me a better pivot table?  Say with a parameter to pick for each month.

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now