last two years in a chart

[francis_gr]

Hi!!

How can I show in a chart, by default, the last two years? (only in this chart) Year is a dimension and i can select the years in a

list box.

I appreciate any help

Regards

[swuehl · Accepted solution]

What do you mean with 'by default'? Should a selection in Year show the default or the selected years? Or any other condition?

Try maybe an expression in your chart like

=If(GetSelectedCount(YearField),

     Sum(Value),

     Sum({<YearField = {$(=Max(YearField)),$(=Max(YearField)-1)}>} Value)

)

The second Sum() uses Set Analysis to create a specific Year filter for the last two years.

[francis_gr]

Hi Stefan!

Thanks for your reply.

Your expression is perfect and solves my question, but, abusing a little of your patience, I would need to know what would be the expression to show on the same chart, for the selected years , the average of "val" field por the people with the same "Pto" field

Sincerely thanks for your help.

Please, see my attached file

[swuehl]

Maybe something like

=If(GetSelectedCount(Year),

      Avg(

      Aggr(

      Avg(total<Year,Pto> Val)

    , Year, Pto,Num)),

    Avg({<Year = {$(=Max(Year)),$(=Max(Year)-1)}>}

      Aggr(

      Avg({<Year = {$(=Max(Year)),$(=Max(Year)-1)}>}  total<Year,Pto> Val) // Count({<Year = {$(=Max(Year)),$(=Max(Year)-1)}>} DISTINCT Num)

    , Year, Pto,Num))

)

But if you want to add this as another expression in your existing line chart with already existing two dimensions, this will not really work well, I think. But it should work in a table chart or if you use the expression as part of your line chart expression (for example as denominator to normalize your values per person).

[francis_gr]

Ok Stefan! That’s pretty close.

Just need that for the year(s) selected, if I select a name , line chart shows the selected person and the average val of all persons with the same job (pto field).

If I select a “pto” it must show all persons with the same “pto” and the average “val” for all of them.

Thanks for your suday time.

Regards

[fema3773]

I got this to work perfectly except when I need to add a second condition.  It shows no data to display.  This is what I am trying to use:

=If(GetSelectedCount(Year),

Count({<[Cause (MIDAS)] = {'Communication failures'}>}[MIDAS Record ID]),

Count({<Year = {$(=Max(Year)),$(=Max(Year)-1)>} {<[Cause (MIDAS)] = {'Communication failures'}>[MIDAS Record ID]>))

Do you know how to fix so I can default to the past two years for only records that have a Cause[MIDAS] that = Communication Failure?