selected date in set analysis

[zturaiki]

Good Afternoon ,

I need help in this Scenario :

I have the following rwo data ...

Patient File No.	Cash IPD	Cash OPD	Insurance IPD	Insurance OPD	Package IPD	Package OPD	Transaction   Month	Transaction Year	Charge Month	Charge Year
KK002231356						1	7	2012	7	2012
KK002231357						1	7	2012	7	2012
KK002233498						1	7	2012	7	2012
KK002233574	1		1		1	1	7	2012	7	2012
KK002225181		1		1			6	2012	7	2012
KK002225181	1		1	1	1		7	2012	6	2012
KK002228933	1		1		1	1	7	2012	6	2012
KK002227150	1		1		1	1	7	2012	6	2012
KK002228078							6	2012	6	2012
KK002238196	1		1		1	1	7	2012	7	2012
KK002228923	1	1	1	1	1		7	2012	7	2012
KK002228923	1		1	1			7	2012	6	2012

I would like it to be present as following :

Year	Month	Cash IPD	Cash OPD	Insurance IPD	Insurance OPD	Package IPD	Package OPD
2012	7	3	8	9	17	6	9
2012	6	4	1	0	6	0	0

Cash IPD = Count ({<Cash IPD={'1'}>}Patient File No)

Cash OPD = Count ({<Cash OPD={'1'}>}Patient File No)

Insurance IPD = Count ({<Insurance IPD={'1'}>}Patient File No)

Insurance OPD = Count ({<Insurance OPD={'1'}>}Patient File No)

Package IPD = Count ({<Package IPD={'1'}>}Patient File No)

Package OPD = Count ({<Package OPD={'1'}>}Patient File No)

the problem is on the date :

the Cash IPD and Cash OPD - it base on the charge Month and Charge Year

the Insurance(IPD/OPD) and the Package --- it base o the Transaction Month and Transaction Year

in my dashboard ,, i want the user to select the year and the month one time ,

attached is a simble data on excel

[Gysbert_Wassenaar]

See attached qvw.

[zturaiki]

Thank You , solution work but it slow down the load of the Data model .. i am looking for faster solution

[CELAMBARASAN]

Hi,

G Wassenaar solution will be faster. How you are saying that it slow down the load. Actually Sum is faster than count.

Could please explain it better? I dont understand why it is slow