Adding standard deviation to a 12 month line chart

[zakpullen]

Hi,

I have a 12 month line chart with this expression:

SUM({<MonthYear={">=$(=Date(MonthStart(Max(MonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear)),'MMM-YY'))"}>}[PDR %])

The dimension is MonthYear (essentially the last day of the month)

I want to add standard deviation as a reference line. This needs to adjust with the chart if a different MonthYear is selected, i.e. the standard deviation should be based on the 12 data points in the chart.

Many thanks

[alejandroquinones · Accepted solution]

Hey @zakpullen ,

You may try the following expression with the inner set expression

Stdev({<MonthYear={">=$(=Date(MonthStart(Max(MonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear)),'MMM-YY'))"}>} 
  TOTAL Aggr(
    Sum({<MonthYear={">=$(=Date(MonthStart(Max(MonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear)),'MMM-YY'))"}>} [PDR %]), 
    MonthYear
  )
)

Or with the outer set expression

{<MonthYear={">=$(=Date(MonthStart(Max(MonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear)),'MMM-YY'))"}>}  Stdev(TOTAL Aggr(Sum([PDR %]), MonthYear))

Greetings,

Alex

[zakpullen]

Thank you so much. They both work.

[alejandroquinones]

Glad it worked!