Problem with Set Analysis and Nested IF Clause

[Anonymous]

Hi Geniuses,

I have a nested if expression below

=if(Account_Type='Cost',if(([Actual]>[Budget]),if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100)),if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))),

if(Account_Type='Expense',if(([Actual]>[Budget]),if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100))),if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))),

if(Account_Type='NA',if((([Var]/[Budget]) * 100)>100, '>100%' ,if((([Var]/[Budget]) * 100)<-100, '<(100%)' ,(([Var]/[Budget]) * 100))

))))

I have one field called Description_Budget

What I want is that my expression should not be affected on the selection of Description_Budget, what is the way of doing it

I think it can be done by set analysis but I am unable to make that set analysis with such a big expression, please help me

Thanks,

S

[tresB]

Where do you use this expression? In a chart or textbox? Does it work without a selection? Don't you use any aggregation function (like sum, count,...) around this expression?

[Anonymous]

Hi Tressco,

I have used this expression in a chart to calculate variance %

I have used the already calculated expression in a chart like Actual, Budget, Variance to calculate the variance %

Actual =num(Sum({<Description_Budget=,Description=,LABEL={'Actual_Interim'},YEARID = {$(=YearVariable)}>} AMOUNT) ,'#,##0;(#,##0)')

Budget =num(Sum({<Description_Budget=,Description=,LABEL={'BudgetUpdated'},YEARID = {$(=YearVariable)}>} AMOUNT) ,'#,##0;(#,##0)')

Var = Actual - Budget

Thanks,

S

[tresB]

I still haven't got where your if(... expression is being used. Is it a variable definition? Could you post a sample qvw?

[Anonymous]

If is used in the variance %

Actual =num(Sum({<Description_Budget=,Description=,LABEL={'Actual_Interim'},YEARID = {$(=YearVariable)}>} AMOUNT) ,'#,##0;(#,##0)')

Budget =num(Sum({<Description_Budget=,Description=,LABEL={'BudgetUpdated'},YEARID = {$(=YearVariable)}>} AMOUNT) ,'#,##0;(#,##0)')

Var = Actual - Budget

Var% =

if(Account_Type='Cost',if(([Actual]>[Budget]),if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100)),if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))),

if(Account_Type='Expense',if(([Actual]>[Budget]),if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100))),if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))),

if(Account_Type='NA',if((([Var]/[Budget]) * 100)>100, '>100%' ,if((([Var]/[Budget]) * 100)<-100, '<(100%)' ,(([Var]/[Budget]) * 100))

))))

[tresB]

Have you tried using this variable? Is it working with/without selection?

[Anonymous]

Yes it is working without selection of Description_Budget, you can see in my IF clause I have Account_Type as Cost Expense NA but When I am selecting the Description_Budget it does not have any account type and hence as soon as I select Description_Budget it is filtering the data out. and Var% is not coming

When I calculate Actual Budget there is no Account_Type so they are coming in the chart on selecting Description_Budget as well as I am putting Description_Budget = in the set analysis

Haven't tried it in variable

[tresB]

Could you post your sample qvw?

[Kushal_Chawda]

Please post the sample