Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sindus123
Contributor III
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
jyothish8807
Master II
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

View solution in original post

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

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

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

sindus123
Contributor III
Contributor III
Author

I need it in front end.

arunaerra
Partner
Partner

Hi  Kavya,

try below expresion.

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

jyothish8807
Master II
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
sindus123
Contributor III
Contributor III
Author

Thanks everyone for response...