topic Moving average/delta in QlikView

Moving average/delta

Tue, 30 Jul 2013 22:38:32 GMT

Hi all,

I have data containing a large list of applications and # of sessions in each application by month. I want to attempt to track the increase or decrease in usage (via # of sessions) by month, and eventually list the applications in order of number of consecutive months with a negative delta (starting with most recent month).

I have something like this:

Count(aggr(count(DISTINCT SessionID)-above(count(DISTINCT SessionID)), DateInvoice) < 0)

In the end, want something like this:

System # Mos Negative

App1 6

App2 4

App3 4

App4 3

...

Please help!

-Paul

Re: Moving average/delta

Wed, 31 Jul 2013 13:35:05 GMT

Anyone want to take a stab at it? The equation above is in the expression of my table, but it does not produce the results as desired. I'm sure it's not too tough, but I can't figure out what I need to aggregate.

Re: Moving average/delta

Wed, 31 Jul 2013 14:39:10 GMT

I think I'm getting closer. Now I have:

=Count(aggr(count(DISTINCT SessionID), Rank(MonthName(DateInvoice))) -

above(aggr(count(DISTINCT SessionID), Rank(MonthName(DateInvoice)))) < 0)

Still not quite right, but maybe on the right track?