Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • Last Modified:

MTD and YTD Pivot table

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
Kdankwah
Asked:
Kdankwah
  • 10
  • 9
1 Solution
 
gowflowCommented:
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
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:
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
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
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
 
gowflowCommented:
ok so the issue is different now, pls post the pivot and I will work on the rest.
gowflow
0
 
KdankwahAuthor Commented:
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
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
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
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
Oh I see, the %Used field is a calculated field which is created by using a formula.

Thanks
0
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
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
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
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
 
KdankwahAuthor Commented:
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
 
gowflowCommented:
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
 
KdankwahAuthor Commented:
Can you give me a better pivot table?  Say with a parameter to pick for each month.

Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now