Sorry, I tend to think like this and forget I have to explain to others, I would make it a member so you can take advantage of the calculation everywhere (smart view, forms, etc) and be able to set the data type as I describe, Hyperion tends to be hierarchy / member / metadata-centric and that is no bad thing.
Hi mb1824 ,
Behaviour of VariancePercent varies when you calculate it in Rows and columns.
Reason behind your problem is, you are calculating VariancePercent between two same AccountType(Revenue, Expense... e.t.c) Accounts so it is not showing the value in terms of %.
if you calculate VariancePercent between Expense account and Revenue Account(need not be expense and revenue ideally it should be two different Account types) then it will show you the value in %.
point to be noted here is you are calculating VariancePercent in Column of a form.
Below is the example. I hope it Clarifies your problem.
As you are calculating variance for the same Account Type so it is not displaying the Value interms of %.
still you need to get VariancePercent for same Account Type in terms of % then you have to bring the Account dimension into rows. This will solve your problem.
Below is the screenshot for your reference.
Thanks for the response, that seems like very strange behaviour to me.
While your proposal may solve my problem, that layout does not meet the business requirement, which is as per the screenshot in my original post
Ok, 'Actual' and 'Budget' are members of [Scenario]. I have set up 2 more members:
- 'Act vs Bud' with member formula @VAR("Actual","Budget")
- 'Act vs Bud %' with member formula @VARPER("Actual","Budget")
As per the below screenshot:
- It seems like the Data Type of [Account] on the Row takes preferences over the column as 'Act vs Bud %' for 'SALES No' is incorrectly formatted.
- The calculation of 'Act vs Bud' and 'Act vs Bud %' when [Account] has Data Type Percent are incorrect.
What am I doing wrong now?
Do you have two-pass enabled on the 'Commercial No %' assuming it is dynamically calculated?
Is 'Commercial No %' dynamically calculated?
What is your evaluation order?
In the above screenshot, 'Commercial No %' is dynamically calculated with two-pass enabled.
I have played around a bit:
- If I disable two-pass on 'Commercial No %' then 'Act vs Bud' calculates correctly although it displays with the % symbol outside the parenthesis for negatives ie (3.4)% when I want it to look like (3.4%).
- However, if I then change Period from 'Sep' to 'Q1' .... 'Commercial No %' calculates incorrectly at 1 decimal place for 'Actual' and 'Budget'.
I am pretty sure I have found this behaviour before, hence I started adding formulas to forms/report instead of setting up the Variance as a member.
My evaluation order has [Account] #1 with nothing else specified.
Do you have any other % based zero level members that are Stored as opposed to calculated, I suspect your issue is trying to do a dynamic calc on a dynamic calc, and if you could test it on a stored percentage value then this would prove / disprove my hypothesis.
I don't have any % based zero level members that are stored.
I was wondering ... what is the reason for choosing Data Storage option 'Dynamic Calc and Store' instead of just 'Dynamic Calc'? Is this something that could help in my situation?
I have never seen anyone use 'Dynamic Calc' and store in practise, but in theory it saves it calculating the second time you access the same combination of dynamic calc members, so it is possible that on your first iteration it would be incorrect, on your second correct - if you find this satisfactory you are unusual in this respect!