Calculated measure in IDT

[former_member214693]

Hi,

In my Universe I need first of all these three measures:

• Total Number Of Listed People
• Number Of Listed People With diseaseX
• Part Of Total with diseaseX in percentage (Number Of Listed With diseaseX / Total Number Of Listed People)

The first measure is of course always the same, but besides the “diseaseX”-measures, we also have similar measures filtered on other dimensional values AND calculated part of total in percentage will be needed for them as well:

• Number Of Listed With diseaseY
• Part Of Total with diseaseY in percentage (Number Of Listed With diseaseY / Total Number Of Listed)
• Etc.

Number of Listed with diseaseX

(Listed people with diseaseX that have been in contact with us)

SELECT

count (distinct DWMart.BI_User.vD_Listed.D_Patient_dkey)

FROM

DWMart.BI_User.vF_VP_Handelse RIGHT OUTER JOIN DWMart.BI_User.vD_Listed ON (DWMart.BI_User.vD_Listed.D_Patient_dkey=DWMart.BI_User.vF_VP_Handelse.D_Patient_dkey AND DWMart.BI_User.vD_Listed.Aktuell_Listed_Status='Exists')

WHERE

( DWMart.BI_User.vF_VP_Handelse.D_Disease_dkey = 'X' AND DWMart.BI_User.vD_Listed.Aktuell_Listed_Status = 'Exists')

Total number of Listed (All listed people)

SELECT

count (DWMart.BI_User.vD_Listed.D_Patient_dkey)

FROM

DWMart.BI_User.vD_Listed

WHERE

( DWMart.BI_User.vD_Listed.Aktuell_Listed_Status='Exists')

So far so good. Both the above SQL-questions returns the correct values. Now we want to use these collected values to calculate a percentage (at least that was what I thought would happen).

Part Of Total with DiseaseX in percentage (Number Of Listed With DiseaseX / Total Number Of Listed)

According to the IDT syntax standard the above measures are referenced according to below from the Business Layer. It is multiplied with 100 for percentage.

(@Select(Measures\Measures Listed\NumberOfDiseaseX)/@Select(

Measures\Measures Listed\TotalNumberOfListed))*100

HOWEVER! When looking at the generated SQL-script, there is no consideration at all for the ”DiseaseX-filter” (WHERE DWMart.BI_User.vF_VP_Handelse.D_Disease_dkey = 'X'…) which is the one thing specifying that this Is a part of the total…

Part Of Total with DiseaseX in percentage - Automatically Generated SQL

SELECT

(( count (distinct DWMart.BI_User.vD_Listed.D_Patient_dkey) )/( count (DWMart.BI_User.vD_Listed.D_Patient_dkey) ))*100

FROM

DWMart.BI_User.vF_VP_Handelse RIGHT OUTER JOIN DWMart.BI_User.vD_Listed ON (DWMart.BI_User.vD_Listed.D_Patient_dkey=DWMart.BI_User.vF_VP_Handelse.D_Patient_dkey AND DWMart.BI_User.vD_Listed.Aktuell_Listed_Status='Exists')

The above SQL is (of course) totally wrong!

So, my question is:

How do I calculate a percentage measure, using two other measures in IDT/Universe???

1. Either I want to do my calculation of the two SQL-scripts in sequel (calculate Number of Listed with DiseaseX and calculate Total number of Listed, use results in new calculation).
2. Or the automatically generated “merged” SQL using my measures in the Business Layer need to be correct!

How is this normally done in the Universe???

I know how to solve this in BeX, conneced to SAP BW and I also have a solution for doing this in Webi.

According to SAP this calculation cannot be done in Design Studio and must be done in Universe. https://archive.sap.com/discussions/thread/3677729

Please advise

Sofie H