Solved

MTD and YTD Pivot table

Posted on 2014-04-28
19
964 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40029147
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 30

Expert Comment

by:gowflow
ID: 40029158
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
ID: 40029249
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 30

Expert Comment

by:gowflow
ID: 40029295
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 30

Expert Comment

by:gowflow
ID: 40029416
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
ID: 40029500
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 30

Expert Comment

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

Author Comment

by:Kdankwah
ID: 40031133
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 30

Expert Comment

by:gowflow
ID: 40031595
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
 

Author Comment

by:Kdankwah
ID: 40031691
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 30

Expert Comment

by:gowflow
ID: 40031795
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
ID: 40031972
Oh I see, the %Used field is a calculated field which is created by using a formula.

Thanks
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40032002
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
ID: 40032057
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 30

Expert Comment

by:gowflow
ID: 40032121
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
ID: 40032292
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
ID: 40032881
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 30

Expert Comment

by:gowflow
ID: 40033379
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
ID: 40040656
Can you give me a better pivot table?  Say with a parameter to pick for each month.

Thanks
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 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