How to write set expression coming from this sql query?

[aresb]

Hi everyone,

I'm writing a set expression for this calculation coming from OBIEE

(cast(sum((CASE WHEN measure1<
(cast(sum(measure2*cast(10 as double)/100) THEN 1 ELSE 0 END)

and here it is:

sum(if(measure1<measure2*10/100,1,0))

and i think it works pretty well.

But what if i should put this calculation into a set expression for a selected period?

I tried with this calculation but it doesnt work:

sum(if(
only({<cod_year_month= {">=$(=min(cod_year_month))<=$(=max(cod_year_month))"}> }measure1)
<
only({<cod_year_month= {">=$(=min(cod_year_month))<=$(=max(cod_year_month))"}> }measure2)*10/100,1,0)

because nested aggregation are not allowed.

how can i achieve this calculation but only for the selected period in cod_year_month?

[Or]

When using a standard aggregation function such as sum(), the selections are automatically applied to the data. If you've only selected December 2022, your sum will only run on data from December 2022. No set analysis is necessary.

If you're trying to aggregate by something other than the dimensions in your object, you may need to use the aggr() function to wrap the internal Only() functions.

[aresb]

hi @Or 

thanks for the response, but this calculation came from a pivot table where i show in the same time and for different dimensions the CY and PY

if(valuelist(CY,PY)=CY,
sum(if(measure1<measure2*10/100,1,0))
,
//PREVIOUS WITH SET ANALYSIS YEAR

how can i achieve this ? 

[Or · Accepted solution]

sum({<cod_year_month= {">=$(=min(cod_year_month))<=$(=max(cod_year_month))"}> } if(measure1<measure2*10/100,1,0))

Would that not work for your scenario?