Pivot Table Question
Posted by Lukas Weder on November 19, 2001 12:12 PM
Hi
In the page field I wanna have two "date" drop down menus so that I can analyze the data over a specific period of time... (ex. from Feb - Dez)
I couldn't solve this problem... may you can?
thanx
Lucky
Posted by Mark W. on November 19, 2001 12:25 PM
Sounds like you need to create some date Groups...
Provide a small amount of test data, and your
desired results... and I'll step you through
the process.
Posted by Lukas Weder on November 19, 2001 12:55 PM
Re: Sounds like you need to create some date Groups...
ok... in the page field should be these two "date" fields I'm looking for...
In the row field is for example the earned premium and the paid loss...
in the column field are different states... lets say california, illinois and indiana...
now, I wanna know the sum of earned premium and paid loss for each state for the selected period of time (which I selected in the two page fields)...
sorry, my English is very bad... I hope you understand what I mean...
thanx a lot for the help!!!
Lucky
Posted by Mark W. on November 19, 2001 1:50 PM
Re: Sounds like you need to create some date Groups...
Okay, you've described your PivotTable layout.
Now, provide a sampling of data from your list.
Also, we'll need to know the conditional test
involving your 2 dates (e.g., date1<=date2,
date1>date2, etc.).
Posted by Lukas Weder on November 19, 2001 2:27 PM
Re: Sounds like you need to create some date Groups...
ok... let's say:
earned premium / paid loss
jan01
california --> 500'000 / 400'000
illinois --> 300'000 / 250'000
indiana --> 200'000 / 150'000
feb01
california --> 450'000/ 350'000
illinois --> 350'000/ 280'000
indiana --> 250'000 / 260'000
mar01
california --> 380'000/ 320'000
illinois --> 360'000/ 290'000
indiana --> 290'000 / 230'000
so, if I select in page field 1 jan01 and in page field 2 mar01, the sum for california earned premium should be (500'000+450'000+380'000=1'330'000)
as conditional test seems "date1<=date2" to be good... isn't it?
thanx again!
lucky
Posted by Mark W. on November 19, 2001 3:23 PM
Re: Sounds like you need to create some date Groups...
You didn't provide the field names from your
data list, but I'll presume that your sample
data looks like this...
Date State Earned Premium Paid Loss
1-Jan california 500 400
1-Jan illinois 300 250
1-Jan indiana 200 150
1-Feb california 450 350
1-Feb illinois 350 280
1-Feb indiana 250 260
1-Mar california 380 320
1-Mar illinois 360 290
1-Mar indiana 290 230
You can Group the 'Date' field into quarters
(thus Jan, Feb and Mar will be combined into Q1).
1. Create a PivotTable with 'Date' in the ROW
area and 'Earned Premium' in the DATA area.
2. Right click the 'Date' field button and choose
Group and Outline | Group... command from the
popup menu.
3. Change the "Ending at" date to 12/31/2001.
4. De-select "By" Months, select "By" Quarters
and press [ OK ].
5. Drag the 'Date' field button to the PAGE area
and choose it's "Q1" item value.
6. Configure the PivotTable to your liking and
you're done!
