Skip to Content
0
May 14, 2018 at 10:48 AM

Multiple where clause in a dimension/measure

1511 Views Last edit May 14, 2018 at 10:51 AM 2 rev

Hi there,

I am creating universe in which i have to show the projection numbers based on the records of last month.

For example the formula for the same will be ,

(select count(Table1.Column1) from Table1 where(Table1.DateColumn2 between add_months(trunc(sysdate,'Month'),-1) and add_months(sysdate,-1)) / select count(Table1.Column1) from Table1 where(Table1.DateColumn2 between add_months(trunc(sysdate,'Month'),-1) and last_day(add_months(sysdate,-1))) )*100

Apparantly, this formula requires two where conditions applied simultaneously in a single dimension. I thought of creating two separate dimensions named partial and total and applying where condition in each of the dimension. But when I am creating a third dimension ProjPercent with formula

(@Select(Measures\partial)/ @Select(Measures\total))*100.

but it is returning me 100. When analysing the script for the dimension ProjPercent, I found out that no where condition is applied and the script is

(select count(Table1.Column1) from Table1 / select count(Table1.Column1) from Table1)*100

Please find the screen shots for reference. Can anybody help me in this. I have done this previously in webi but now I have to do the same in IDT to use it in design studio. partial.jpg total.jpg projpercent.jpg

Attachments

partial.jpg (55.8 kB)
total.jpg (59.1 kB)
projpercent.jpg (51.8 kB)