0 Replies Latest reply: Jun 6, 2017 5:30 PM by Ruth Farrell RSS

    need help making a combo chart with cumulative FYTD result as well as a monthly min & max result

    Ruth Farrell

      Hi all, I'm looking for some help to make a combo chart (column and marker) that reports a FYTD cumulative result per site, with a marker for each sites' minimum monthly result and maximum monthly result.

       

      The result is a ratio, calculated in the graph as an expression and linked to a dimension that filters the data to the FYTD result or the current month result.

       

      The cumulative result expression is this:

       

      if(Dim='Month',

      (sum({<KPIKEY={'XX'}, Category = {'X'},MONTHYEAR={'>=$(=Monthstart($(vXXPeriod),0))<=$(=monthend($(vXXPeriod),0))'}>}Denominator)

      /sum({<KPIKEY={'XX'}, MONTHYEAR={'>=$(=Monthstart($(vXXPeriod),0))<=$(=monthend($(vXXPeriod),0))'}>}Denominator)),

      (sum({<KPIKEY={'XX'},  Category = {'X'}, MONTHYEAR={'>=$(=yearstart($(vXXPeriod),0,7))<=$(=Monthend($(vXXPeriod)))'}>}Denominator)

      /sum({<KPIKEY={'XX'}, MONTHYEAR={'>=$(=yearstart($(vXXPeriod),0,7))<=$(=Monthend($(vXXPeriod)))'}>}Denominator)))

       

      In the attached Excel example, the above formula is represented by the grey columns. I need some help to make the min and max results, represented by the green and blue markers.

       

      Thank you in advance!

      Ruth