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
Solved

MTD and YTD Pivot table

Posted on 2014-04-28
19
901 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
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 29

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 29

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 29

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 29

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 29

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 29

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 29

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 29

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 29

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 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