Set Analysis

[wonkymeister]

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

[israrkhan]

try like this.

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

[MK_QSL · Accepted solution]

Try this

Previous one was wrong.. try this

[stevedark]

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/

[wonkymeister]

thanks for all your help - all sorted now.

[wonkymeister]

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

[stevedark]

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