topic Last 3 years - Dynamic in QlikView

Last 3 years - Dynamic

sathishkumar_go — Mon, 27 Sep 2010 05:37:55 GMT

Hi All,

I have barchart it shows year wise the data. now I want to do based on selection i want to display in last 3 years.

Ecample : if i select 2009-2010

in barchart it will show 2009-2010,2008-2009,2007-2008

without macro it is possible?

-Sathish

Last 3 years - Dynamic

Mon, 27 Sep 2010 05:46:02 GMT

HI Sathish,

You can use Set Anslysis feature to do this. Which will be like as under:-

SUM({$<Year={">=$(=YearName(Max(Date), -2, 4))<=$(=YearName(Max(Date), 0, 4))"}>} Sales)

So if you see in above example I am doing it for financial year which start from Apri. But if you are using calendar year then it's more simple like as under:-

SUM({$<Year={">=$(=Year(Max(Date))-2)<=$(=Year(Max(Date)))"}>} Sales)

Last 3 years - Dynamic

sathishkumar_go — Mon, 27 Sep 2010 06:07:06 GMT

Hi Dhanajey,

Thanks for your Quick Reply. But My problem is based on selection it will show last 3 years.

example : In list box if i select 2008-2009

then barchart will display on only 2008-2009,2007-2008,2006-2007

-Sathish

Last 3 years - Dynamic

Mon, 27 Sep 2010 06:36:25 GMT

Hi Sathish,

This will do the same. When you won't select any year it will consider max year to get last 2 years as and if you select any year then it will consider that year to get last 2 years.

So pls test this in ur scenario it works fine. I am using QlikView 9.0 SR5.

Last 3 years - Dynamic

Mon, 27 Sep 2010 06:49:58 GMT

Hi Satish ,

I think this expression will help you this for current year ,current month ,previous month, and previous-1 month selection .....

previous month:

=count({$<YM={"$(vLast1YearMonth)"},Year=,Quarter=,MonthName=,BUCKER={1} >} distinct DebtNo)

current month:

=count({$<YM={"$(vCurrentyearmonth)"},Year=,Quarter=,MonthName=,BUCKER={'1'}>} DISTINCT DebtNo)

Previous -1 month

=count({$<YM={"$(vLast2YearMonth)"},Year=,Quarter=,MonthName=,BUCKER={1} >} distinct DebtNo)

here VLAST1YEARMONTH IS THE VARIABLE SO YOU HAVE TO MAKE THESE TYPE OF VARIABLE FOR CURRENT AND PREVIOUS YEAR .....

or you can use attached application for your refrence......

Last 3 years - Dynamic

suniljain — Mon, 27 Sep 2010 08:15:08 GMT

Dear Sthish,

Pls check logic of set analysis dynamic tab of attached qvw. I implemnted same thing with the help this example in sales application.

Regards

Sunil jain.

Re: Last 3 years - Dynamic

ramu8543 — Sat, 14 Sep 2019 07:18:57 GMT

could u please syntax and elaborate explanation ,dynamically change in last 3 years data