11 Replies Latest reply on Oct 31, 2018 11:03 AM by Robert Angel

    Data Forms - Display % correctly

    mb1824

      Hi,

       

      I use PBCS.

       

      In the below form, column D is a formula - VariancePercent([A],[B]).

       

      When I preview the form, the formula calculates correctly (0.079), but it is not displayed as 7.9%.

       

      How do I fix this?

       

      Oracle_Form_Display%1.JPG

      Oracle_Form_Display%2.JPG

        • 1. Re: Data Forms - Display % correctly
          Robert Angel

          Set the data type on the dimension member; -

           

          • 2. Re: Data Forms - Display % correctly
            mb1824

            Robert Angel

             

            It is a Formula, not a dimension member??

            • 3. Re: Data Forms - Display % correctly
              Robert Angel

              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.

              • 4. Re: Data Forms - Display % correctly
                Avinash Bhuma

                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.

                 

                • 5. Re: Data Forms - Display % correctly
                  mb1824

                  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

                  • 6. Re: Data Forms - Display % correctly
                    mb1824

                    Robert Angel

                     

                    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:

                    1. 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.
                    2. The calculation of 'Act vs Bud' and 'Act vs Bud %' when [Account] has Data Type Percent are incorrect.

                     

                    What am I doing wrong now?

                     

                    Oracle_Form_Display%3.JPGOracle_Form_Display%4_Act vs Bud %.JPG

                    • 7. Re: Data Forms - Display % correctly
                      Robert Angel

                      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?

                      • 8. Re: Data Forms - Display % correctly
                        mb1824

                        In the above screenshot, 'Commercial No %' is dynamically calculated with two-pass enabled.

                         

                        I have played around a bit:

                        1. 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%).
                        2. 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.

                        • 9. Re: Data Forms - Display % correctly
                          Robert Angel

                          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.

                          • 10. Re: Data Forms - Display % correctly
                            mb1824

                            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?

                            • 11. Re: Data Forms - Display % correctly
                              Robert Angel

                              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!