Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

Latest three years data for each company

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...

1 Solution

Accepted Solutions
Highlighted
Honored Contributor II

Re: Latest three years data for each company

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

Best Regards,
KC

View solution in original post

6 Replies
Highlighted
Contributor

Re: Latest three years data for each company

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)

Highlighted
Valued Contributor

Re: Latest three years data for each company

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

Highlighted
New Contributor III

Re: Latest three years data for each company

I need it in front end.

Partner
Partner

Re: Latest three years data for each company

Hi  Kavya,

try below expresion.

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

Highlighted
Honored Contributor II

Re: Latest three years data for each company

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

Best Regards,
KC

View solution in original post

Highlighted
New Contributor III

Re: Latest three years data for each company

Thanks everyone for response...