Set analysis: show the last value of the last year

[#1]

Hi Community!

My table is the following:

YEAR	MONTH	DATE	VALUE
2015	6	30/06/2015	38
2015	5	31/05/2015	37
2015	4	30/04/2015	30
2015	3	31/03/2015	33
2015	2	28/02/2015	33
2015	1	31/01/2015	14
2014	12	31/12/2014	52
2014	11	30/11/2014	48
2014	10	31/10/2014	49
2014	9	30/09/2014	46
2014	8	31/08/2014	47
2014	7	31/07/2014	45
2014	6	30/06/2014	49
2014	5	31/05/2014	53
2014	4	30/04/2014	57
2014	3	31/03/2014	60
2014	2	28/02/2014	73
2014	1	31/01/2014	100

  
And I'm trying to display in a bar chart a double expression:

- the first one must show the last value of the current year: OK, I managed to get it by using the function max(DATE) within the set analysis.

- the second one must show the last value of the last year in order to compare with the current one (38) with the last average of the last year (52).

Do you know how could I obtain the max(DATE) of the max(YEAR-1)?

Thanks in advance to all of you!

Regards,

Vincent

[Gysbert_Wassenaar]

Use YEAR as dimension and FirstSortedValue(VALUE, -MONTH) as expression.

[sunny_talwar]

Try this may be:

DATE = {"$(=Date(Max({<YEAR = {'$(=(Max(YEAR) - 1))'}>}DATE), 'DD/MM/YYYY')"}

[#4]

Thanks Gysbert, but in that case, it doesn't work at all because I have 4 years of results (the data attached above were just a small sample), but I always want to show only 2 years. I've tried with Incidents Type (my first dimension) and Year, but it didn't work (my expression is  =FirstSortedValue(Sum(VALUE), Month(DATE))

Any idea about how to restrict the Dimension in order to show always only 2 years?

Thanks again!

[#5]

Thanks Sunny, but no success for today 😞

If you have another idea, I'll be happy to ear it!

Thanks again for your help!

[Gysbert_Wassenaar]

Why not simply select those years?

[#7]

Because I need this bar chart to be interactive. If I select 2014, I'll pretend to visualize the last record of 2013 and the last record of 2014. That's whay I cannot use YEAR={'2015'} and YEAR={'2014'} in the different expressions of the Set analysis 😞

[Gysbert_Wassenaar]

Why not simply select those years?

[#9]

Sorry, I didn't understood you before. The user wants the screen to be displayed without any selection. That's why I cannot work with triggers.

[Gysbert_Wassenaar]

Let me rephrase that... Why not let the user select the years he/she is interested in?