Last 3 years - Dynamic

[sathishkumar_go]

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

[#2]

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)

[sathishkumar_go]

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

[#4]

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.

[#5]

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

[suniljain]

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.

[ramu8543]

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