Multiple where clause in a dimension/measure

[former_member562331]

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

[marton_retfalvi]

Hi,

I checked the screenshots but it seems you use in "partial" and "total" exactly the same query and referring to the same table.

Because of this, you get the same results in both "partial" and "total".

Example: partial = total = 34

After you use the following operation:

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

(34/34)*100=(1)*100 = 100.

The problem is that in both partial and total you use the same where condition.

But as I've seen in the description in the 'total' where condition you wanted to use this:

between add_months(trunc(sysdate, 'Month'),-1) and last_day(add_months(sysdate,-1))

instead of

between add_months(trunc(sysdate, 'Month'),-1) and add_months(sysdate,-1)

Marton

[former_member562331]

Hi Marton,

Thanks for replying,

Seeing through business standpoint, total refers to the numbers we accumulate in the whole month previous to the present date whereas partial will refer to the numbers accumulated till the same date as sysdate, but in previous month.

Explaining this by an example,

If today is 14 May 2018 then partial results will be the numbers attained from 1 April 2018 till 14 April 2018 whereas total results will refer to that attained from 1 April 2018 to 30 April 2018.

After I get these numbers, I have to calculate the percentage increase in the numbers in the previous month from 14 April 2018 towards the end of the month the formula for which will be given by partial/total *100.

Hope you understood.

The values of measures partial and total when viewed independently are giving the desired results owing to the where condition used with them. But when I am using them in a third measure to calculate the projection percentage, there in no where condition and hence the result is false.

Manish