Variance Expression

[sona_sa]

Hi,

I am developing a report where I am using pivot objects. Also I am using alternative states for developing  the same reports.

I have written the code for adhoc reports for expression, Whre I have 4 option to do the analysis the same report and I am just checking the variance.

1. Selection à P1 Actual vs P2 Actual = Output (P1 Actual – P2 Actual)
2. Selection à P1 Actual vs P2 Budget = Output (P1 Actual – P2 Budget)
3. Selection à P1 Budget vs P2 Actual = Output (P1 Budget – P2 Actual)
4. Selection à P1 Budget vs P2 Budget = Output (P1 Budget – P2 Budget)

For P1 Actual vs P2 Actual : It is working and another 3 selection is not working, So please look into this and try to resolve it asap.

For more reference I am attaching my application, please find the attached qvw.

Thanks in Advance.

  A Kumar

[Gysbert_Wassenaar]

You use the field [Deb/Cred (SY)] in the expressions for the budget. That field only contains nulls. Check your source data, because that's where the problem is.

[sona_sa]

Thanks for reply, Now please find the attached qvw. If there is 0 please give me output as :

• Selection à P1 Actual vs P2 Budget = Output (P1 Actual – P2 Budget)
• Selection à P1 Budget vs P2 Actual = Output (P1 Budget – P2 Actual)
• Selection à P1 Budget vs P2 Budget = Output (P1 Budget – P2 Budget)

Whatever the output should to come.

Please find the attached qvw for the same.

Thanks Again.

[#4 · Accepted solution]

There's a problem with getcurrentselections in your if condition in 'Variance' expression.

and also in the if condition, you don't need to use [P&L Type]='s' when you already have mentioned it in the base expressions, which you are using them here. Whenever you're referring your existing expressions for a new expression, try to put base if conditions in your base measures/expressions only. As in the if condition they are not aggregated values and it will never act as expected, which will return a null value. You need to use

sum(if(condition, value))

for that.

Try this.

=

If((Match(GetFieldSelections(D02_Dimension),'Actual') and Match(GetFieldSelections(D03_Dimension),'Actual')),([P1 Actual]-[P2 Actual]),

If((Match(GetFieldSelections(D02_Dimension),'Actual') and Match(GetFieldSelections(D03_Dimension),'Budget')),([P1 Actual]-[P2 Budget]),

If((Match(GetFieldSelections(D02_Dimension),'Budget') and Match(GetFieldSelections(D03_Dimension),'Actual')),([P1 Budget]-[P2 Actual]),

If((Match(GetFieldSelections(D02_Dimension),'Budget') and Match(GetFieldSelections(D03_Dimension),'Budget')),([P1 Budget]-[P2 Budget])))))

[sona_sa]

Thanks Eddie