how to make single graph view by quarter and month?

[Anbuselvam K]

Hi,

As usual i have my company sales quantity by month wise in graph. It's from Jan'2013 to Nov'2016. Totally 47 months. I need a graph with view of quarter division except current month

I would like to make a graph view in quarterly from Jan'2013 to Dec'2015 and month view to Jan'2016 to Nov'2016.

Is it possible to make single graph with two types of view? If yes please guide me to do the same

Sincerely Yours
Anbuselvam k

 

[Chihiro]

Use PivotTable to summarize your data. It has native grouping for Quarter. Then use PivotChart to plot your data.

 

[Anbuselvam K]

Dear Chihiro

Thanks for your reply!

I've used Pivot Chart but grouping option is available only quarter, yearly and monthly. Now i've used monthly and yearly.

I need current month data by month view and before all the year data in quarter view in a single graph. How do i get the same please guide me?

If possible please give me an example file with pivot grouping for my study

 

[Chihiro]

I'm not sure that I understand you. Upload sample file with 2 graphs representing what you want to see in single graph.

 

[Anbuselvam K]

Dear Chihiro

Thanks for your reply!

Please find the attached copy of file pivot chart with quarter view from 2015 to 2017. Its an example file. Actually i've a data from 2011 to 2016 as same as attached sheet.

if i make monthly view in the graph, totally 72 months of value is showing which is not covered by full screen view. So i would like to make the graph with quarter view from 2011 to 2015 and month view for the current year in single pivot chart.

I hope you understand my requirements. Please do the needful!

 

[AVK]

Dear Anbuselvam : Why not using "scroll bar" graph.

 

[r2c2]

@Anbuselvam K

You can't get such a semi-grouped chart with single pivot chart. But you can add two extra columns to your original data, one for year and another for period. Year will use YEAR(date) and period uses IF() to switch between showing Quarter or Month based on which year the date falls in to. Once you have such a setup, a pivot chart on year, period & sum of qty should work for what you want.

See attached workbook.

Thanks,

 

[Anbuselvam K]

I got it

Really Thanks

 

[halfmanhalfspreadsheet]

Hi,

As it happens, this vlookup tutorial covers the exact situation that you are looking to solve for.

(Source: www.excelvlookup.com)

Example there may be quarterly sales numbers that you would like to view but currently you only have the date of each sale.
This is possible using a VLOOKUP. Remember the structure is:
=VLOOKUP ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )
Example Quarterly Data VLOOKUP Scenario
In the example, we would like to populate column C in the Data tab so that there is a quarter associated with each Sales Date (in column A).



By including the Financial Year in the data, it will enable us to include an automatic summary table (see Summary tab in the Tutorial Workbook) and will allow us to fill in the Sales Total column below

In order to do this, we will need to create a lookup table which in the example file is done on the Lookup tab. Importantly, the lookup values in column A are the start months for each quarter.






Solution vlookup formula
So using the standard structure of

=VLOOKUP( SearchFor , WhereToSearch , WhichColumn ,NearOrExact )

where SearchFor is based on the Sales Date.
This will be based on the month part of the date, so our SearchFor term will use the MONTH excel formula -> MONTH(A2)
WhereToSearch is the list of Dates and Financial Years -> Lookup Tab A:B
Which column is the second column -> 2
NearOrExact is Near -> True
Our formula looks like =VLOOKUP(MONTH(A2),Lookup!A:B,2,TRUE)

 

[Anbuselvam K]

Thanks a lot for your reply!

 

[AVK]

Dear Anbuselvam, As per my our earlier quot i have suggest you, using scroll graph. In attach file i preparing month wise scroll graph, as far as your requirement you required qtrly & current fy month wise, you can make changes accordingly. This is actually for your grooming purpose.