Latest three years data for each company

[Anonymous]

Hi All,

I want to display latest 3 years of data.

If i select company B it should consider 2015,2014 and 2013.

For company A it should consider 2014,2015 and 2016.

LOAD * INLINE [

    RecNo, Date, Value, company

    1,     2014,  5,     A

    2,     2015,  10,    A

    3,     2016,  15,    A

    4,     2012,  10,    B

    5,     2013,  15,    B

    6,     2014,  20,    B

    7,     2009,  20,    C

    8,     2010,  25,    C

    9,     2011,  30,    C

  10,     2015,  25,    B

];

Thanks in advance...

[manas_bn]

One way to do this: Create a Rank Column using the date for each company.

Fact:

INLINE [

  RecNo, Date, Value, company

    1,     2014,  5,     A

    2,     2015,  10,    A

    3,     2016,  15,    A

    4,     2012,  10,    B

    5,     2013,  15,    B

    6,     2014,  20,    B

    7,     2009,  20,    C

    8,     2010,  25,    C

    9,     2011,  30,    C

  10,     2015,  25,    B

];

// Join Rank to Fact table

LEFT JOIN (Fact)

LOAD

RecNo

,if(company=Previous(company),peek(Rank)+1,1) as Rank

RESIDENT Fact

ORDER BY company, Date DESC;

Now use set analysis in the expression to get Ranks 1, 2 and 3: Sum({<Rank={1,2,3}>}Value)

[arvind1494]

Do you need this in back end or on front end only?

[Anonymous]

I need it in front end.

[Anonymous]

Hi  Kavya,

try below expresion.

=sum({<Date={">=$(=max(Date)-2)<=$(=max(Date))"}>}Value)

[jyothish8807 · Accepted solution]

Hi Kavya,

A simple set analysis will do in this case. Try like below:

Sum({<Year={'>=$(=max(Year)-2)<=$(=max(Year))'}>}Value)

The value of $(=max(Year)-3) will change according to the company selection.

Also it depends what chart you are using and what is your dimesnsions.

Br,

KC

[Anonymous]

Thanks everyone for response...