2 Replies Latest reply on Dec 13, 2012 11:11 AM by Mayo Ibarra

    How to work with formulas as or in place of Measure Names

    Mayo Ibarra

      Hi,

      (this is for crosstab view)

       

      I'm aware of using a calculated field to define names you would like to use. I've also been successful in getting the same sort of conditional field name necessary for what I need... However, when I put this alongside the normal measure names and have values in place, their is no certain way to allow the default names dimension and the formula resultant name dimension to exist alongside one another. Either one or the other becomes above or below on the column header even if on the measure value side of things, the proper value is the only value showing.

       

      I'm assuming the solution is to adjust the calculated field to include all the measure names necessary in one dimension. I'm at a loss how to do this especially when the measure item I made with exactly the same formulation properly shows only that value and doesn't impose both at all.

       

      My calculation is basically the following:

      If [Actual sales] do not equal zero or [if this is not the current month], then show the [Actual sales figure] else show the [Estimation figure]

      The column name formula is the same just that I make the result either one string text or the other (Actual or Estimations).

      The result is that for past and current months we have actual sales and future months we have estimated figures.

       

      It seems that with my calculated field for the names does not take into account the conditional and shows both resultants no matter what with no regard for the values present. So even if I could get the other static name included I'm not sure it is going to work. I'm assuming I would need to include some sort of reference to the calculated measure value in the calculation for the names dimension to get what I want.

       

      I believe I may be missing something in my methods... Any tips?

       

      Thank you...

        • 1. Re: How to work with formulas as or in place of Measure Names
          Catherine Rivier

          Hi,

          I'm not entirely sure I get exactly what kind of end result you're looking to see.  Your calculations sound correct overall.  But I do wonder what you're trying to accomplish with your column name calculation.  If you are showing all months from 2012-2013, where half are Sales and the other half are Forecasted Sales, what do you want the column heading to be?  You likely don't need a column name header at all - just name the Measure calculation selecting the appropriate Sales an acceptable column name.

           

          See the attached sample workbook and let me know if this is what you're looking for.  Otherwise, can you share a workbook or even an Excel mockup showing what you'd like your final view to look like, and I will take a look.

           

          But hopefully this helps!

          Catherine

          • 2. Re: How to work with formulas as or in place of Measure Names
            Mayo Ibarra

            Hi Catherine,

            Yes, your example has the basic idea in place. Especially in the single select area.

             

            However its another step more complicated with the name portions. I see the same sort of issue when we expand to look at "All" Months, which is what Duplicate Columns also shows. It involves labeling the "sales selection" properly when it shows one or the other sales figure.

             

            I have attached an example of how the original report from BO works out. I can definitely go by and just put a label that shows that we can have values of either, but in the original reporting software I had been able to arrange for it to change with the data/time as well.

             

            I thank you for your assistance. I realized my data extract to work on this trial has a flaw and I have to redo it before I test/recreate some other aspects of the original report...