3 Replies Latest reply on Jul 25, 2016 7:32 AM by Naveen Agarwal

    Constant reference curve overlay

    Roy Weckiewicz

      I am displaying the CAGR (Compound annual growth rate) at various geographic levels: Regional, sub-regional, country,  between the range of years on my year slider. What I would like to do is also overlay a constant global CAGR, which is simple enough to do by just removing the geographical constraints. See my attached image. Currently I have the global curve overlaid as a dual axis and synchronized with the country level curves both on the same sheet. My issue is that I would like this overlay (Black curve - Global CAGR) to stay constant and unaffected by changes in the geo filters. Currently if I deselect a country, region, or sub, the global curve changes along with everything else (the end year rate changes). I've tried using a reference line from the global axis, but this exhibits the same behavior. I don't want a straight line reference, the reference needs to be the actual global CAGR calculation.  I apologize I don't have an extract of this yet, as I need to create a version without proprietary data.

        • 1. Re: Constant reference curve overlay
          Naveen Agarwal

          How are you calculating the Global CAGR? I think it would be best to have a calculated field for Global CAGR using an LOD expression with FIXED as one of the attributes.

          • 2. Re: Constant reference curve overlay
            Roy Weckiewicz

            Hi Naveen,


            As these are still early steps, I've been using the standard CAGR equation similar to the built-in one:


                 POWER(ZN(SUM([Quantity (kbd)]))/LOOKUP(ZN(SUM([Quantity (kbd)])), FIRST()),ZN(1/(INDEX()-1))) - 1


            It seems the LOD expressions don't play well with table calculations though, as I get the error: LOD expressions cannot contain table calculations or ATTR function.

            It seems the FIXED attribute would probably solve what I'm trying to do. Any ideas how I can get around this error?

            • 3. Re: Constant reference curve overlay
              Naveen Agarwal

              Hi Roy


              Yes it is correct that the LOD function will have a conflict with Table calculations if are using aggregation like SUM in the formula.


              Since you are using First() in the formula for CAGR, it will change depending on the relative position of the individual row with respect to the first row in the table - which is determined by the filters or other selections you make in your table.


              Can you post your data? I think it will help people to review and provide suggestions.