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

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

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.