Log In
Register
Home
Active Member
July 24, 2020
Offline
Dear Administrators,
I really need some help in completing my task . I've prepared my file to sent where I've stated my challenges, however at this point I can't see attach button thus could not attach it. How can I attach my file and will you be considering to help?
Thank you for you prompt reply.
Erdenebileg
October 5, 2010
Offline
Hi,
Happy to help when we see your file.
Did you check this which explains how to attach a file?
https://www.myonlinetraininghu.....this-first
regards
Phil
Active Member
July 24, 2020
Offline
Dear Phil,
Thank you so much for the prompt reply, and really appreciate for your help. Here in attached my file. Although, I've tried to explain the problem in the file, I will try to put it here as well.
Basically, before I've have been preparing my invoice in 3 steps to get correct amount.
Step 1. Find Percentage to charge the Monthly fee to each company (# people (Company A) *100% / Total # of people )
Step 2. Multiply the Monthly fee to percentage in the table. and then in pivot table field setting (selected as Average) to get the correct amount.
Challenges facing:
1. Above technique I can no longer use, as I'm trying to automate a bit using the query function of excel. Thus, the correct amount is shown when the field setting (selected Sum).
2. In the pivot table to be able to filter our Invoicing period which starts on 26th of each month until the 25th the following month.
3. This monthly fee varies depending the low season and high season (from 1March to15 December / From 15 December to 1 March) this step I will need to consider when I get to the cleaning my data for the past period. Still need to find solution. However, I didn't state this in the file.
I've tried my best to explain it, hopefully it is clear enough. Again, thank you so much for your willingness to help.
Erdenebileg
October 5, 2010
Offline
Hi Erdenebileg,
I'm not sure I've totally understood everything because you mention in Step 1 finding a percentage charge based on number of people but I don't see data for this in the workbook.
However if the main issue is getting the pivot table to summarise data based on your fiscal months then you need to use a technique as described here
https://www.myonlinetraininghu.....-and-years
https://www.myonlinetraininghu.....scal-years
I've created a new table which is used to map your periods, the 26th-25th of each month, to a reporting month e.g. Nov 19. A new column in your source data table is then created which shows the reporting month for each record.
You can now create your pivot table (see Pivot sheet in attached file) which correctly averages the Weight values for you.
Regards
Phil
Active Member
July 24, 2020
Offline
Dear Phil,
Thanks for the Fiscal Year table that you did, way better solution than I came up with. Really appreciate that. Here in attached the real data of the Residents, also I put the question differently with hope that it will be clearer then previous one, sorry for the inconvenience. Thank you again.
Regards,
Erdenebileg
October 5, 2010
Offline
Hi Erdenebileg,
No worries.
Sorry your explanation isn't clear still. You refer to 'the expected result is to get correct values' but I don't know what those 'correct values' you expect are. You need to be explicit please.
Do you want the Average of Weight shown in the pivot table values rather than the Sum? Isn't that what I did in the workbook I attached in Reply #4?
You can't have the value field aggregation set to Sum and then get Averages in the Pivot Table. You will have to set aggregation for the Values to Average and then sum the columns for each period manually. You've already done this on the Pivot sheet.
Regards
Phil
1 Guest(s)
