How to show products over an agreed date in a pivot table

[applesandpears]

Hi all, am new and appreciate any help any of you kind and experienced people can provide. I have an interview after taking 5 years off on maternity leave!! and my excel skills are not what they used to be!!

I need help on the following:


1)      Build pivot to calculate things like:


-          Average lead time per order type

-          Average lead time per platform

-          Orders breached SLA per month for the last six complete months.


2)      Represent graphically:

a.       Monthly order growth per customer

b.      Current order status for those started in the last two months.


I have the data table below:


start Date end date Product /order type Customer Sales lead time per order(days) platform SLA Agreement days

12/09/2012 20/09/2012 Mobile Customer14 $45,446.00 20 platform yellow 15

13/09/2012 22/10/2012 fixed line Customer20 $43,703.00 34 platform purple 15

17/09/2012 23/10/2012 gsm Customer11 $45,446.00 20 Platform blue 15

19/10/2012 24/10/2012 handset Customer5 $97,346.00 20 platfom white 15

19/09/2012 25/10/2012 icloud Customer6 $45,446.00 50 platform red 15

20/09/2012 26/10/2012 iphone Customer15 $52,107.00 120 platform black 15

23/09/2012 14/11/2012 samsung Customer12 $52,107.00 30 platform purple 15

23/09/2012 15/11/2012 Mobile Customer8 $43,703.00 30 platform red 15

24/10/2012 16/11/2012 fixed line Customer7 $52,107.00 50 platform yellow 15

25/09/2012 17/11/2012 gsm Customer20 $45,446.00 22 platform black 15

 

[Sajan]

Hello @applesandpears,


I would encourage you to try a Google search for "Excel Pivot Tables Tutorial". Even this site has a related "how to" article from 2009. I would encourage you to review some of them, and try out some of the examples. Afterall, practice will help you feel more confident in your interview.

After trying some of the tutorials, if there are specific areas that are not clear, please ask!


Good luck in your interview.


-Sajan.

 

[applesandpears]

hi Sajan,


thank you for your reply and I have been doing lots of tutorials and I can figure out the averages and perfecting this all day!!!

I still cannot find a tutorial that shows me due dates/agreed dates etc in pivot tables? can you help or point me in the right direction?


Once again, thank you for your time


Amarjit

 

[Sajan]

Hi Amarjit,


Not sure what you mean by due dates/ agreed dates. Are you asking about how to group date values? Here is a link to a tutorial from the Contextures web site, describing how to group dates, among other things.


http://www.contextures.com/xlPivot07.html


The site also has links to other things related to Pivot Tables.


Hope that helps.


-Sajan.