topic Re: Previous year Data in QlikView

Previous year Data

Anonymous — Wed, 08 Apr 2015 09:16:23 GMT

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

Re: Previous year Data

pokassov — Wed, 08 Apr 2015 09:18:02 GMT

Hi!

if(GetSelectedCount(Year)=1,

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

Sum(YourMeasure)

)

Re: Previous year Data

Wed, 08 Apr 2015 09:23:41 GMT

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

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

EndDate==num(Max(Date))

Re: Previous year Data

matthewjbryant — Wed, 08 Apr 2015 09:31:22 GMT

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.

Re: Previous year Data

Anonymous — Wed, 08 Apr 2015 09:34:01 GMT

without using set analysis we can do it or not

Re: Previous year Data

pokassov — Wed, 08 Apr 2015 09:35:18 GMT

Cannot

Re: Previous year Data

matthewjbryant — Wed, 08 Apr 2015 09:36:51 GMT

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

Re: Previous year Data

Anonymous — Wed, 08 Apr 2015 09:42:34 GMT

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

Re: Previous year Data

jagan — Wed, 08 Apr 2015 09:45:29 GMT

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

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.

Re: Previous year Data

pokassov — Wed, 08 Apr 2015 09:48:02 GMT

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.