expression in pivot table

[zturaiki]

I have this row data for a list of admission patient in a hospital

.... ( Patient File No.   - Bed No - Admission Date -- Discharge Date )

Patient   No.	Bed_No	Admission Date	Discharge Date
K357621	3451A	6/23/2013	-
K302412	3544A	5/1/2013	-
K349836	4406B	6/15/2013	-
K359798	4407B	6/25/2013	-
K360008	4422	6/25/2013	-
K349621	3445B	6/15/2013	-
K332622	4405B	5/29/2013	-
K339366	3210	6/4/2013	6/6/2013
K337072	3204A	6/6/2013	6/6/2013
K350981	3204B	6/20/2013	6/20/2013
K350128	3212B	6/23/2013	6/23/2013
K357775	3217B	6/24/2013	6/24/2013
K360606	3211B	6/27/2013	6/28/2013
K360255	3218	6/26/2013	6/28/2013
K362984	3219	6/29/2013	6/30/2013
K308969	4303	5/7/2013	6/4/2013
K345927	3336K	6/11/2013	6/15/2013
K348907	3402	6/15/2013	6/16/2013
K353348	3507C	6/24/2013	6/24/2013
K343523	4314	6/9/2013	6/12/2013

The user ask these questions :

1- Total No. of New Admission in selected year

for example :: if the user select :

Month(AdmissionDate ) = 7

year(Admission Date ) = 2013

Total No. of Patiient admitted in this year/month is = 16 patients

2 - Total No. of Existing Patient in selected year who is still admit but not discharge

for example :: if the user select :

Month(AdmissionDate ) = 7

year(Admission Date ) = 2013

select All patient where

admission Month(AdmissionDate ) <= 7 and year(Admission Date ) <= 2013 and month(discharge date)>7 and year(discharge date) >=2013

i would like to represent the data in a pivot table like the attached picture --- as an example ,,, but i face problem in

cacluating the existing patient ,, it gave me zero

Admission   Year	Admission Month	Existing Patient	No. Of New   Admission
2012	Jan	0	1125
	Feb	0	1110
	Mar	0	1069
	Apr	0	1195
	May	0	1147
	Jun	0	1067
	Jul	0	1017
	Aug	0	983
	Sep	0	1037
	Oct	0	1007
	Nov	0	1102
	Dec	0	1128
2013	Jan	0	1092
	Feb	0	1014
	Mar	0	1071
	Apr	0	1101
	May	0	1123
	Jun	0	1190
	Jul	0	1103
	Aug	0	954

attached is a sample of data in excel

[#2 · Accepted solution]

Hi,

in this case usually  I create a fact table like this.

For example the row

Patient No.	Bed_No	Admission Date	Discharge Date
K323063	4605	5/30/2013	7/7/2013

is linked to these facts:

YEAR_	MONTH_	countAdmission	countExist	countDischarge
2013	May	1
2013	Jun		1
2013	Jul			1

The expression in pivot table will be sum(countExist)

View attached file.

[zturaiki]

Thanks for the solution ,,, it help me ...