• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic year to date calculation

G

geneth

New Member
Hi,


I have a list of monthly data that spans about 11 years. I have created a slider that controls graphs on a dashboard that show rolling 13 months (this is the easy part). I now need to show a table that compares the last month on the (eg Feb 2012) with the same month last year (this is easy) as well as the financial year that that month falls into compared to the same period in the previous year eg Oct 2011 to Feb 2012 vs Oct 2010 to Feb 2011. The point of the dashboard is that the user must just click on the slider and everything else must just work! I don't want them to enter any info at all. Everything that I have tried has failed. Please help.
 
Can you explain how the financial year works? Will the slider always go from CurrentMonth to Oct, or does the slider control the October part, or does it control the CurrentMonth (Feb)?


Also, it there just the one slider bar that controls both the 13 month rolling window and this latter table, or are there 2 separate sliders?
 
Hi Luke,


The slider controls monthly data. As you slide, the graphs scroll across displaying 13 months. There is a table which showns the CurrentMonth compared to last year as well as financial year (Oct to CurrentMonth). This is where I have a problem because the OffSET formula (which controls the slider, graph and monthly data on the table) moves down 1 month at a time. I haven't a cooking clue how to sum a variable number of months (for the financial year) and compare it to the same number of months in the previous year. Does this make sense? How do I attach a file to this post to illustrate more clearly what I need?
 
Hi geneth,


You can upload file to any free file hosting website. You can find some here:


http://chandoo.org/forums/topic/posting-a-sample-workbook


Faseeh
 
Hi ,


If the slider linked cell points to any particular month , is it really a date value or just a month number ?


If it is a date value , shouldn't it be possible to offset it by any desired value to get whatever you want ?


Narayan
 
Hi,


I have uploaded a file to https://hotfile.com/dl/149698705/68f5964/Market_Shares.xlsm.html


I hope that this helps.
 
Hi geneth,


I am working with following assumption on your table:


1. Your financial year starts from Oct and Ends in February.

2. You want a month-to-month comparision, If user selects Oct, 03, it will be compared to Oct, 4th?

3. For any month selected, You want that month to the end-of-the-financial year data. viz if user selects Nov, 03, you want Nov, 03 to Feb, 03 compared with Nov, 04 to Feb, 04??


Kindly see whether this year-to-date concept is correct or not? Besides what about the idea of 'comparing this month with that month i.e. two dorp-downs to select month and comparing and similar for years??


Regards,

Faseeh
 
Hi geneth,


Here is the workout file:


http://dl.dropbox.com/u/60644346/Market%20Shares_Myworkout.xlsm


In this file you can compare any three months. In the table below the years for those months will be compared automatically (for averaged value for that FY). I am still unable to understand the concept of Year-To-Date calculation. If you can explain in plain words with an example, how can arrive to the YTD result that is to be displayed in the yellow highlighted cells in your sheet, the remaining part could be completed.


Faseeh
 
Hi Faseeh,


Our financial year is from Oct - Sep. The year to date calculation (for the whole year) is thus sum(Oct:Sep). If it is only Feb, then the calculation will be sum(Oct:Feb).


The formula in the yellow block F21 would thus be =SUM(Data!G122:G126) and in G21 it would be =SUM(Data!G110:G114).


Of course the trick is that as the slider moves, this formula needs to change. So if you move the slider back so that the date in B1 is Sep-11 then the formula in F21 needs to be: =SUM(Data!G110:G121) and in G21 =SUM(Data!G98:G109)


Does tha make more sense?


Geneth
 
Hi,


I get an erro whe I open the file, will check through.


However, I think that OFFSET would work for this problem
 
Hi geneth,


Kindly look into this file i think the issue has been fixed:

http://dl.dropbox.com/u/60644346/Market%20Shares%20V2.xlsm


...am waiting for your feedback and would suggest you to improve the cosmetic look of graphs.


@ kchiba


Hi,


I didn't know how to use offset in this situation, I have used SUM(INDEX()) instead.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top