topic Re: Variance Expression in QlikView

Variance Expression

sona_sa — Mon, 29 Dec 2014 16:52:25 GMT

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.

Selection à P1 Actual vs P2 Actual = Output (P1 Actual – P2 Actual)
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)

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

Re: Variance Expression

Gysbert_Wassenaar — Mon, 29 Dec 2014 17:22:22 GMT

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.

Re: Variance Expression

sona_sa — Mon, 29 Dec 2014 18:14:01 GMT

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.

Re: Variance Expression

Mon, 29 Dec 2014 21:17:01 GMT

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])))))

Re: Variance Expression

sona_sa — Tue, 30 Dec 2014 05:09:34 GMT

Thanks Eddie