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:



      (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!