Running 30 day average

[madsgrathe]

Hi everybody

I'm having trouble getting QlikView to do what I believe is probably a relatively simple task...

I'd like it to calculate the running 30 day average of a certain expression. In this case the average Sales per User within the last 30 days before a given date. So for any date it should calculate the average based on the preceeding 30 days.

Here's how I calculate my current average Sales per User:

sum({<LoginDays={'>4'}>} Sales)/count({<LoginDays={'>4'}>} distinct UserID)

I'm using set analysis because I only want to count the users who have a certain amount of LoginDays, in this case 5.

Can anyone help me do a 30 day running average based on this calculation?

Thank you very much in advance for any advice you can give me. I'm learning to use QlikView the hard way and any help is much appreciated 🙂

[lironbaram]

hei attach is an example

hope it helps you

[madsgrathe]

Hi

Thank you very much for the reply, but I'm not quite sure I understand your suggestion. Using your example I tried to apply it to my own data in the graph in question:

sum({<LoginDays={'>4'},Date={">=$(=max(Date)-30) <$(=max(Date))"}>} Sales)
/count({<LoginDays={'>4'},Date={">=$(=max(Date)-30) <$(=max(Date))"}>} distinct UserID)

But that just seems to give me the daily Sales per User for the last 30 days of my time selection. I have Date as my dimension variable. Am I doing something wrong?

[madsgrathe]

Could I somehow use rangesum and rangecount to achieve this?

[rbecher]

Hi,

we're using something like this:

rangeavg(above(sum(Sales),0,30))
rangeavg(above(sum({$<LoginDays={'>4'}>} Sales),0,30))

But, I'm not sure about the set analysis with your field LoginDays... Try it first without set analysis.

- Ralf

[madsgrathe]

Hi Ralf

It's possible to do what you suggest both with and without set analysis - but it's not really what I was looking for.

Firstly, your solution gives me the average DAILY sales for the last 30 days. I was looking to also divide this average with the number of users, so it should also count the number of Users within that time period and divide the sales by that count.

Secondly, It's not dynamic. In the sense that it begins counting from the first Date I select as my timeinterval. When in fact it should count from that day -30 days. Do you see my point? So a sort of dynamic accumulation of Sales and dynamic count of Users and the two divided with each other. If that makes sense...

[madsgrathe]

An easier way to explain it would be that for any given date, I need to calculate the total Sales divided by number of distinct users within the last 30 days of that date.

[rbecher]

this is hard to say without knowing your data and application...

[jonathandienst]

Hi

Above works OK (with or without the set expression), but it only takes into account data that is in the table. If there are missing days, then above(..,30) will include more tham 30 days in the expression. It also cannot look back beyond the start of the table, if you have some other selections active (such as a Month field selection)

I expect that a better approach would be to use a date island. I have attached a demo of this which hopefully will help you.

[rbecher]

I have no idea right now but you could fill the empty days with Sales=0....