Set Analysis support required

[Saravanan024]

I need a trend line on a line graph for EVERY year with the analysis condition listed below. I'm just receiving output for a single year right now, so I'm wondering whether there's any way to change it such that several trend lines for prior years are displayed.

Aggr(RangeSum(Above(TOTAL (sum( {$<Year={$(=max(Year)-1)}>} Quantity_Consumed ))

/

Sum({$<[TICSA],Year={$(=max(Year)-1)}>} TICSA)

  *

 (

RangeAvg(Above(Count({$<Year={$(=max(Year))}>} Equipment), 0, RowNo())))

 , 0, RowNo(TOTAL))),Month)

[JandreKillianRIC · Accepted solution]

Hi @Saravanan024 

Depening on the number of years you want to see I would just change the set Analysis to (This should show 4 years) But for this to work you need Year as a Dimension. 

Aggr(RangeSum(Above(TOTAL (sum( {$<Year={"<=$(=max(Year)-4)"}>} Quantity_Consumed ))

/

Sum({$<[TICSA],Year={"<=$(=max(Year)-4)"}>} TICSA)

  *

 (

RangeAvg(Above(Count({$<Year={$(=max(Year))}>} Equipment), 0, RowNo())))

 , 0, RowNo(TOTAL))),Month)

Alternatively you can add multiple measures for "each" year. (Just change these parts in the set analysis) 

So change the Max(Year)-1 (Prev year) 

So change the Max(Year)-2 (2 Years ago) 

So change the Max(Year)-3 (3 Years ago)

Regards Jandre