# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
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.

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

];

1 Solution

Accepted Solutions
Master II

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
6 Replies
Creator

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)

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)

Specialist

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

Contributor III
Author

I need it in front end.

Partner

Hi  Kavya,

try below expresion.

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

Master II

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
Contributor III
Author

Thanks everyone for response...