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

[#2]

I wish I could convince my user in order to oblige him to do this, but unfortunately, he did not accept this proposal 😞

[MK_QSL]

You can use as below

Dimension

YEAR

Expression

=FirstSortedValue({<YEAR = {"<=$(=MAX(YEAR))>=$(=MAX(YEAR)-1)"}>}VALUE,-MONTH)

Hope this what you are looking for !

[#4]

Unfortunately, no, because I have more than one value for the same month in some cases (I've tried to add a SUM to the expression, but it didn't work

(=FirstSortedValue(sum({<YEAR = {"<=$(=MAX(YEAR))>=$(=MAX(YEAR)-1)"}>}VALUE),-MONTH)

Thanks a lot for your help!

[MK_QSL]

Provide data with more than one value for the same month along with your expected output.. I will try.!

[jagan · Accepted solution]

Hi,

Try like this

For Current Year:

=Sum({<YEAR =, DATE={'$(=Date(Max(DATE)))'}>}VALUE)

For Previous Year:

=Sum({<YEAR =, DATE={"$(=Date(Max({<YEAR={'$(=(Max(YEAR) - 1))'}>} DATE)))"}>}VALUE)

Hope this helps you.

Regards,

Jagan.

[#7]

OMFG! It works! Thanks a lot Jagan!

[jozisvk11]

Hello, I have problem with set analyse. I want to filter only values, where is the rows max. I have this, but it is not good.

Sum ({<[Rank of line] = {"$(=max([Rank of line])"} >} [Sum of income]). Thanks for helping me.

Here is my table.

ID Purchase          Row          Income  

N01                      1                  200

N02                      1                  50
N02                      2                  70

N02                      3                  60

N03                      1                  10

N03                      2                  35

N04                      1                  10

N04                      2                  40

N04                      3                  60

N04                      4                  90

N05                     1                  20

Set Analayse should choose me only this table.

ID Purchase          Row          Income  

N01                      1                  200

N02                      3                  60

N03                      2                  35

N04                      4                  90

N05                      1                  20