In a query I have a case statement that gives either of two values for Attend Interview - this is worked out from another field that gives one value for 3 different scenarios that actually mean attend = Y, one value covers not attend = N. So for example I have something like this in my output:
Unique_ID New/Existing Attend_Interview
========= ======================== ================
12554445 E Y
65766879 N N
53375654 N Y
44323224 E N
93656786 E Y
What I then do is put this into a summary grid in Microstrategy (MS) to work out the new/existing applications further broken down by Y/N for attend.
But I also want to find out the ratio of New Application Attend to New Application Not Attend, however can't do that in MS since I am using a crosstab summary report and therefore need to try and calculate in SQL if possible.
My summary looks something like this at the moment:
N E
====================== =========================
**Y** N **Y** N
========= ========== ========= =========
570 140 89 56
And I would like to work out the ratio of 570/89 but as I said I think I need to do something in SQL so that I can put this in to my summary report as these are not physical columns I can divide.
Many thanks in advance for any suggestions on how to create this ratio field or suggestions for rework what I am doing.
Using : SQL Server 2008 R2 Express and web version of MS
You can create two conditional metrics for Attend_Interview = Y and Attend_Interview = N cases, then you can calculate the ratio between these two metrics.
If you want to use these metrics in you in your crosstab report you should replace the Attend_Interview attribute with the metrics.