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