Performing a calculation on a "Total" field from two separate queries?

[seballen]

Hello Coggies,

Is it possible to perform a calculation on fields from two separate queries. Both fields are Totals (i.e. the Aggregate Function and the Rollup Aggregate Function are set to Total) and I need to calculate a percentage.

I can't generate both numbers in one query.

The report requires:

Dx: Total People With X Diagnosis
Poor Control: Total People With X Diagnosis who have not had Text Z or Results of Test Z are out of range

Percentage of People with X Diagnosis with Poor Control

Thanks,
Sasha

[PRIT AMRIT]

For this you first have to establish a JOIN between these two queries based on a common column. Then create a calculated item and define your calculation(Percentage of People with X Diagnosis with Poor Control) on your final query.

Hope it helps.

[seballen]

OK, so no way to just use the total values from un-joined queries in a calculation? Seems so strange that I can't just divide one total value by another...

I have many joins in this report and am hoping to speed it up.

[barrysaab]

seballen,i think there is no other way out,you have to go with the join between these two queries.Thanks

[Lynn]

You can union instead of join.

Both queries have a column for each metric, so the Dx query returns the Dx figure and the Poor Control figure is just a dummy expression that returns 0.

The Poor Control query does the opposite....it returns 0 for Dx and the actual poor control figure.

Then union those two queries and do the percentage calculation in the union result.

[barrysaab]

Thanks,Lynn.Supposetwo queries are similar in datatype but one of them has one data item more item,in this scenerio we can't do uion but we left with only join option or we have any other options left.

[cognostechie]

What Lynn said is that you can create a dummy data item with 0 value which will make the no. of data items same in both the queries so you can union them.