Rolling 30 Days Average via Chart

[mattparker]

Hi everyone!  I've been a long time lurker of these forums. I've read a ton in the last 3 years I've been using QlikView.

I haven't been able to solve my issue via lurking (or even find something close to it), so I finally made an account. 

Here's the scenario:

I've been asked to find the average cost of an item for the last month, but on a specific day...  i.e. The average cost of a toothbrush for the last 30 days on July 12th, 2017 (so the average cost of the toothbrush between June 13th and July 12th).  That part is simple.

But now here's the tricky part:

I need to be able to show this average cost across the lifespan of my data.  i.e. The average cost of a toothbrush for the last 30 days on July 12th (avg of June 13th - July 12th), July 11th (avg of June 12th - July 11th), July 10th (avg of June 11th - July 10th), etc. Each average will have 30 days of cost values.

I'm not sure what to call it.  A trailing 30 days average or something.  On top of all this, I need to get it into a chart format of sorts - line or bar, I don't care for now. Just trying to get the concept down.

I'm not sure if it's better to get all of this in during the load of the files or using a calculated field in the expressions and/or dimensions.

Any help is appreciated!

[Kushal_Chawda]

try this expression

=Avg({<Date={">=$(=max(Date)-30)<=$(=max(Date))"}>} Cost)

[mattparker]

Yep!  Here you go!  Here's essentially what I'm trying to go for...

Thanks again for your aid!

[mattparker]

Thanks Andrew!  I'll give it a shot.  I really don't want to do it in the script, but it may be the way I have to.  Thanks for your input!

[mattparker]

Well, gave it a shot.  No luck on this end.  I appreciate you jumping in to try and help, however.

[mattparker]

Hi Jean-Baptiste!  Any thoughts on the file I provided to get an idea of what I'm after?  Your help is greatly appreciated!

[MarcoWedel · Accepted solution]

Hi,

if your cost values are valid from date of occurrence until the next recorded date then one solution might be to generate the missing dates e.g. like described in "Creating Reference Dates for Intervals" using Jean-Baptiste's RangeAvg() expression.

So one example might be:

tabTemp:
LOAD Date(Date) as Date,
    Money([Toothbrush Cost],'#,##0 €;-#,##0 €') as [Toothbrush Cost]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1306577-287201/QlikViewExample.xlsx] (ooxml, embedded labels, table is Sheet1)
Where Date;

NoConcatenate

tabCost:
LOAD Date(Date+IterNo()-1) as Date,
    [Toothbrush Cost]
While Date+IterNo()-1 < PrevDate or IterNo()=1;
LOAD Date,
    Previous(Date) as PrevDate,
    [Toothbrush Cost]
Resident tabTemp
Order By Date desc;

DROP Table tabTemp;

hope this helps

regards

Marco

[mattparker]

Hi Marco,

I think this is as close as it's going to get - while I don't like the idea of throwing in dates that don't actually exist, I'll just recode it as a new field so it doesn't confuse anyone.  Thank you for your assistance!