topic Re: Pivot Table - Avg last 5 days in QlikView

Pivot Table - Avg last 5 days

pamaxeed — Fri, 19 Feb 2016 10:50:14 GMT

Hi,

I have a question regarding the following calculation.

I have a pivot table with Account and Date as dimension:

How is it possible to get the AVG Amount of the last 5 days for every single date dimension value?

I am struggling I have tried with Set Analysis but I could not come to a solution

avg({<[Ref Date]={"<=$(=Max([Ref Date]))>=$(=Only([Ref Date]-5)"}>} AMOUNT_BU)

Anyone can help me?

Update:

I have a

dded a sample application, the result in the Avg Last 5 Days is not correct!

The expression I used here is:

avg({<[Date]={'>=$(=Date(Max([Date])-5))<=$(=Date(Max([Date])))'}>} Amount)

Kind regars,

Patric

Nachricht geändert durch Patric Amatulli

Re: Pivot Table - Avg last 5 days

Chanty4u — Fri, 19 Feb 2016 10:53:39 GMT

avg({<[Ref Date] ={"$(='>=' & Date(vDay-7) & '<=' & Date(vDay))"}>} AMOUNT_BU)

avg({<[Ref Date]={"<=$(=Max([Ref Date]))>=$(=Only([Ref Date]-5)"}>} AMOUNT_BU)

Re: Pivot Table - Avg last 5 days

pamaxeed — Fri, 19 Feb 2016 10:55:17 GMT

Can you explain what you did?

What is behind the vDay variable?

Re: Pivot Table - Avg last 5 days

sunny_talwar — Fri, 19 Feb 2016 10:55:18 GMT

I think you need to Add Date() function to match the formatting of Ref Date with your filter:

Avg({<[Ref Date]={">=$(=Date(Max([Ref Date])-5, 'YourRefDateFieldFormatHere'))<=$(=Date(Max([Ref Date]), 'YourRefDateFieldFormatHere'))"}>} AMOUNT_BU)

Re: Pivot Table - Avg last 5 days

pamaxeed — Fri, 19 Feb 2016 11:02:24 GMT

Is not shown up anything...

Re: Pivot Table - Avg last 5 days

Chanty4u — Fri, 19 Feb 2016 11:04:45 GMT

sry try dis

avg({<[Ref Date]={‘>=$(=Date(Max([Ref Date])-5))<=$(=Date(Max([Ref Date])))’}>} AMOUNT_BU)

Re: Pivot Table - Avg last 5 days

sunny_talwar — Fri, 19 Feb 2016 11:08:15 GMT

Did you replace YourRefDateFieldFormatHere with your date field format or did you just copy paste my expression?

Re: Pivot Table - Avg last 5 days

pamaxeed — Fri, 19 Feb 2016 11:13:28 GMT

I replaced with my Format ('DD.MM.YYYY') but nothing showing up, now I have attached a sample application and used the expression suggested by sureshqv, but nothing....

Re: Pivot Table - Avg last 5 days

pamaxeed — Fri, 19 Feb 2016 11:20:04 GMT

Check my attached sample application...

Re: Pivot Table - Avg last 5 days

sunny_talwar — Fri, 19 Feb 2016 11:21:46 GMT

Didn't realize Date was a dimension, try this:

RangeAvg(Before(Sum(Amount), 0, 5))

Re: Pivot Table - Avg last 5 days

pamaxeed — Fri, 19 Feb 2016 11:26:05 GMT

Great!

Re: Pivot Table - Avg last 5 days

jonas_rezende — Fri, 19 Feb 2016 11:28:51 GMT

Hi, Patric Amatulli

Try:

AVG({<[Ref Date] = {">=$(=DATE(Max([Ref Date])-5)) <=$(=Max( Date([Ref Date])))"}>} AMOUNT_BU)

Hope this helps!

Re: Pivot Table - Avg last 5 days

sunny_talwar — Fri, 19 Feb 2016 11:32:34 GMT

And just make sure to change the expression to this:

RangeAvg(After(Sum(Amount), 0, 5))

if you plan to change the sorting from Ascending to Descending