expression in pivot table

zturaiki — Sat, 31 Aug 2013 11:29:16 GMT

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

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

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

attached is a sample of data in excel

Re: expression in pivot table

Sat, 31 Aug 2013 16:07:18 GMT

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 — Sun, 01 Sep 2013 06:52:00 GMT

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