Calculation (addition and subtraction )in Charts and Text Box

[waqasshah]

Hi All,

I need your help. It seems to be a simple calculation but it  becomes complicated and I am unable to display what I want. I have attached the excel sheet for a sample. One tab is source data the other one is how I would like to display data in chart (straight table )and text boxes.

Following is the explanation

HospitalWardName : Shows show ward in a hospital

Patients In Each Ward: Shows Total Number of  patients  in each ward

Total Beds In Each Ward: Shows Total number of beds in each ward

Free Beds In wards: Shows total number of free wards 

Although I have Free beds in wards field but it did not display  data so I use the following calculation

Free Beds In wards = (Total Beds In Each Ward ) - ( Patients In Each Ward)

Same issue with the TEXT BOX  I would like to display Free Beds In wards but it did not display anything

Can you please help ? Thanks In advance.

Please note in my original work I am using QVD files to get data  and use my database values for calculations. Excel sheet is a reference only 

Kind Regards

WS

[AshvitaKatkar]

Hi Waqasshah,

Try below expression, I hope it will help you to get desire output

For table Chart & Textbox:

PatientsInEachWard : Sum(Aggr(Sum(DISTINCT PatientsInEachWard),HospitalWardName))

TotalBedsInWard : Sum(Aggr(Sum(DISTINCT TotalBedsInWard),HospitalWardName))

FreeBedsInWard: Sum(Aggr(Sum(DISTINCT TotalBedsInWard)-Sum(DISTINCT PatientsInEachWard),HospitalWardName))

Regards,

Ashvita

[waqasshah]

Dear Ashvita,

Thanks for your reply. Can you please upload the QVW file that you worked on ?  I would like to play around.Again many thanks for your help.

Regards

Waqaas Shah

[AshvitaKatkar]

hi Waqasshah,

Sorry for late reply.

PFA.

Regards,

Ashvita