topic Set Analysis in QlikView

Set Analysis

samvile18 — Wed, 17 Aug 2011 09:13:18 GMT

Morning All,

Just a quickie:

I'm using the attached set analysis to calculate the Actual value for the year 2011. What I want to do is change it, so I can look at the latest months value instead. I'm guessing I need to do something with the max and month function but I can't quite figure it out.

Can anyone help me please?

Sum({$<Year = {2011},Measure={'Actual'}>}Value)

Re: Set Analysis

martin59 — Wed, 17 Aug 2011 09:21:09 GMT

Hi,

If you want to work on the latest month, I advice you to do something like this :

Sum({$<Year = {2011},Measure={'Actual'}, Month={"$(=Max(Month))"} >}Value)

Sum({$<MonthYear = {"$(=Max(MonthYear))"},Measure={'Actual'}} >}Value)

And if you want to work on the three latest months, you have to do :

Sum({$<MonthYear = {">=$(=AddMonths(Max(MonthYear),-2))"},Measure={'Actual'}} >}Value)

Hope that helps you

Martin

Re: Set Analysis

lironbaram — Wed, 17 Aug 2011 09:23:07 GMT

hei

try something like this

Sum({$<Year = {2011},Month={"$(=max(Month))"},Measure={'Actual'}>}Value)

assunming month is a numeric field

Set Analysis

samvile18 — Wed, 17 Aug 2011 09:35:26 GMT

That does work, I do however have a problem with my data which could be tricky to get around.

The max month will be 12 as there is budget data loaded until the end of the year, this set analysis therefore returns £0 for the actual data because there's only data loaded up Jul (7) for actuals.

Do you know a way around that?!