Previous year Data

[Anonymous]

Hi All,

I have a requirement like whan i select one year in a list box  it has to displays last five years of data in straight table by excluding current selected year.Please help me frineds it is urgent requirement in my project.

Thanks in advance

[pokassov]

Hi!

if(GetSelectedCount(Year)=1,

Sum({$<Year={'>=$(=max(Year)-5)<$(=max(Year))'}>} YourMeasure),

Sum(YourMeasure)

)

[#3]

(Sum( {$<Date_Num={">=$(=(StartDate))<=$(=(EndDate))"},Year=,Month=,Day=>}  Amount))

StartDate==num(max(addmonths(Date,-60)))

EndDate==num(Max(Date))

[matthewjbryant]

Set up variables =GetFieldSelections(Year)-1, =GetFieldSelections(Year)-2, =GetFieldSelections(Year)-3, ... etc. for as many years as you wish to go back. Call them something like vSelectedLastYear, vSelectedYear-2, vSelectedYear-3,...

Then use set analysis on the columns to only return the previous years you wish in each column

i.e. Sum({$<Year={$(vSelectedLastYear)}>} Value) will return the sum of all values for 2014 if I select 2015.

[Anonymous]

without using set analysis we can do it or not

[pokassov]

Cannot

[matthewjbryant]

Why wouldn't you want to use Set Analysis? In my understanding Qlikview's filtering is centred around Set Analysis.

[Anonymous]

Hi Сергей Покасов,

in my staringt table 'council code effective date' and 'council name' are my dimensions 'agentid' and ' year 'are filters .if a click on agent id and year it will show last 5 years data with out current selected year.can u please give me the detailed code for this

[jagan]

Hi,

Without using set analysis it is no way possible, Qlikview displays data based on the Current Selections.  You can also try introducing a new mapping table like below

LOAD

Year,

,

,

FROM ActualData;

YearMapping:

LOAD

*

INLINE [

Year, Last5YearsField

2015, 2015

2015, 2014

2015, 2014

2015, 2014

2015, 2014

2014, 2013

2014, 2013

2014, 2013

2014, 2013

2014, 2013

'

'

'

];

Like this you have to create mapping table, and then use Last5YearsField for year selection.  This way you can achieve, but it is not suggestible.  Using Set Analysis is the better approach, I am not sure why you are not interested in Set analysis.

Hope this helps you.

Regards,

Jagan.

[pokassov]

I think the expression is the same like I wrote above.

I don't exclude agentid from filter.

So if you select agentid and year, you will see data for 5 years except selected for selected agentid.