topic Set Analysis in QlikView

Set Analysis

avastani — Thu, 16 Apr 2009 22:54:16 GMT

Hi,

How do I get a twelve-month rolling average over a given field using set analysis. I tried Only(Year) -1 and Only(Month) - 12 and Only(Date) - 365 but that doesn't seem to work. I am thinking I need Aggr as well?

Any help is appreciated. Thanks in advance.

Set Analysis

markmccoid — Thu, 16 Apr 2009 23:15:11 GMT

Not sure how you had wanted to implement this, but if you were thinking of creating twelve expressions starting with today's month -12 up to the current month to get the rolling monthly numbers, you could use set analysis as follows:


SUM({$<MonthOfYearNumber={$(=Num(Month(AddMonths(Today(),-12))))},CalendarYear={$(=Year(AddMonths(Today(),-12)))}>} FieldToAggregate)

You would then change the -12 parameter on the AddMonths function to -11, -10, etc.

Hope it helps.

Mark

Set Analysis

avastani — Fri, 17 Apr 2009 00:42:48 GMT

I was thinking of implementing it in one expression as opposed to 12 expressions. I think there is a way, just haven't come around to narrowing the method yet.

Set Analysis

markmccoid — Fri, 17 Apr 2009 01:55:40 GMT

So you want to have one total amount for the last 12 months?

In one statement I think the following would work.

<blockquote><pre>
SUM({$<Insert_MonthOfYearNumber={">=$(=Num(Month(AddMonths(Today(),-12))))"},Insert_CalendarYear={$(=Year(AddMonths(Today(),-12)))}>} $(NetRevenue))
+
SUM({$<Insert_MonthOfYearNumber={"<=$(=Num(Month(Today())))"},Insert_CalendarYear={$(=Year(Today()))}>} $(NetRevenue))

Not sure how to use the union operator in Set Analysis to combine this into one statement.

Mark

Set Analysis

Fri, 17 Apr 2009 03:15:10 GMT

Here is an example of a moving avgerage expression that has the added twist of being dynamic (the slider determines the number of days in the moving average). This is done without Agrr() or set analysis (it dates back pre set anaysis/agg()) instead it uses RangeAverage() and Above().

Sometimes Agg() and set analysis are so powerful they can blind us to other solutions. Just something to think about.

Regards,

Dan

Chart:

Expression:

if(TRADE_DATE > min(total TRADE_DATE) + vMovingAvg, rangeavg(above(avg(CLOSE_PRICE),0,vMovingAvg)), null())

Calculated Dimension:

=if(TRADE_DATE > min(total TRADE_DATE) + vMovingAvg, TRADE_DATE, null())

Data Structure:

Set Analysis

avastani — Fri, 17 Apr 2009 03:15:22 GMT

I think so too. I am going to try now. Thanks Mark

Re: Set Analysis

Wed, 27 Nov 2013 09:24:56 GMT

Hi Dan,

I realise that this was an old post, but I hope you can help me as this seems to be the only solution that doesn't require a slider. For some reason the graphics/table you posted are not being displayed. I tried to apply both formulas, but I get "No data to display" message on the graph. Also, I have two moving averages on one graph and your solution seems to provide for one only via a variable.

Regards,

Lucian