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

Tue, 06 Jun 2017 21:30:02 GMT

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

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

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