topic Re: Set Analysis in QlikView

Set Analysis

wonkymeister — Wed, 02 Oct 2013 08:23:50 GMT

I need some set analysis help if possible?

I have a dataset with these fields:

Year	Version	Counter
2013	Current	25
2013	Previous	30
2012	Current	10
2012	Previous	15
2011	Current	20

when the selected Year on the dashboard = 2013 i need to sum the Counter where the Version = Current AND sum the Counter where the Year = the previous year (so 2012) and the Version = Previous

I'm ok with the first part i.e. =sum({$<Version={'Current'}>} Counter), but adding in the second part logic (adding in the previous year + previous version) is what i'm struggling with

any help greatly appreciated

Re: Set Analysis

israrkhan — Wed, 02 Oct 2013 08:44:20 GMT

try like this.

=sum({$<Version={'Current'}>} Counter) + sum({$<Year=${=max(Year)-1}, Version={'Previous'}, >} Counter)

Re: Set Analysis

MK_QSL — Wed, 02 Oct 2013 09:13:45 GMT

Try this

Previous one was wrong.. try this

Re: Set Analysis

stevedark — Wed, 02 Oct 2013 12:35:36 GMT

Hi Phillip,

You may find this example document helpful - this looks at pulling values out based on prior periods:

http://community.qlik.com/docs/DOC-4313

The trick is to work out the values you want to match to in variables and insert them into your statement - this simplifies things a lot.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

Re: Set Analysis

wonkymeister — Wed, 02 Oct 2013 13:10:24 GMT

thanks for all your help - all sorted now.

Re: Set Analysis

wonkymeister — Thu, 03 Oct 2013 08:31:00 GMT

thanks Steve

the problem actually got worse in that the Year field became a 6 character string: 2011/2

working with dates etc in QlikView baffles me anyway, i tackled the issue in the wrong way by over complicating it until finally seeing the light with a very simple solution by stripping down the Year using Left() and Right(), and applying -1 to both sets then repackaging the value concatenating the '/' in the middle.

I always seem to struggle with set analysis, need to read more!

thanks again

Re: Set Analysis

stevedark — Thu, 03 Oct 2013 08:55:37 GMT

Hi Phillip,

I have in the past had numerical values as well as the proper representation built in the load script. For example, an integer giving number of months since 01/01/2000 - as these can be much easier to handle in Set Analysis than just having date fields.

All the best,

Steve