Set analysis to find current year, year-1, year-2

[darren_dixon]

Hi,

I'd like to use a straight table with 3 expressions to find the total appointments for each financial year.

To achieve this i was trying to change the code below to =[ActualStart.Fiscal Year]-1

count({$<[ActualStart.Fiscal Year]= {'2011-2012'}>} [Appointments])

Priority	Total Appointments	2011-12	2012-13	2013-14
Standard	100	30	20	50
Emergency	25	10	10	5

[montero91 · Accepted solution]

Hi Darren

Try with:

Current Year

count({$<[ActualStart.Fiscal Year]= {$(=Year(Today()))}>} [Appointments])

Year -1

count({$<[ActualStart.Fiscal Year]= {$(=Year(Today())-1)}>} [Appointments])

Year -2

count({$<[ActualStart.Fiscal Year]= {$(=Year(Today())-2)}>} [Appointments])

or if you want the two years anteriors

count({$<[ActualStart.Fiscal Year]= {$(=Year(Today())-1),$(=Year(Today())-2)}>} [Appointments])

Always take the current year of today, but you can use the "max" to be against selected year maximum, if selected 2012 the previous year is 2011.

if you put in the set analysis '2012-2013 ' it is taken as text not like range. is a number the value of ActualStart.Fiscal? or is a Text?

[#3]

Hi Darren,

as I understand it, your field ActualStart.Fiscal Year contains the valies '2011-2012', '2012-2013', ... . Qlikview will interpret this as text, which will make it hard to make your year-1 en year-2 work.

I think the best way is to create an extra field which only contains the start year. You can create this field in the existing table, or create an extra table for this purpose.

ActualStart.Fiscal Year   |   ActualStart.FiscalYearStart

2010-2011                      | 2010

2011-2012                      | 2011

2012-2013                      | 2012

You can then use this new field just the way you were trying.