topic Re: Calculate an average for the last 4 weeks in QlikView

Calculate an average for the last 4 weeks

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following expression in order to calculate CA from the begining of the year till yeasterday

=num(Sum({1< DateTest={"$(='>=' & Yearstart(AddYears(Today(), -1)) & '<' & AddYears(Today(), -1))"}>} [CA] ),'# ##0 ;-# ##0 ')

How to calculate CA average for the last 4 weeks. starting from today()-2

Re: Calculate an average for the last 4 weeks

Chanty4u — Wed, 02 Mar 2016 11:07:42 GMT

chk dis

Re: Last 4 Week Average

Re: Calculate an average for the last 4 weeks

sunny_talwar — Wed, 02 Mar 2016 11:09:12 GMT

Is this a rolling average you are calculating in a chart? or are you calculating this in a text box object? A sample with expected output will be helpful to resolve your issue

Re: Calculate an average for the last 4 weeks

jonathandienst — Wed, 02 Mar 2016 12:12:54 GMT

Something like

Sum({1< DateTest={">=$(=Date(Today() - 28)) <$(=Today())"}>} [CA])

Re: Calculate an average for the last 4 weeks

Anonymous — Wed, 02 Mar 2016 12:25:54 GMT

try like this?

I am assuming your DateTest is in this format "DD/MM/YYYY"

=Sum({<DateTest={">=$(=date(WeekStart(max(DateTest),-3),'DD/MM/YYYY'))<=$(=date(WeekStart(max(DateTest)),'DD/MM/YYYY'))"} [CA])

Re: Calculate an average for the last 4 weeks

Anonymous — Wed, 02 Mar 2016 12:26:49 GMT

Side by side check this as well?

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Re: Calculate an average for the last 4 weeks

Wed, 02 Mar 2016 12:35:03 GMT

Like this

Sum({1< DateTest={">=$(=Date(YearStart(Today()),'DD/MM/YYYY')) <=$(=Date(Today()-1,'DD/MM/YYYY'))"}>}[CA])

change the date format according to your DateTest field date format..

Re: Calculate an average for the last 4 weeks

Wed, 02 Mar 2016 12:53:11 GMT

Hi Bala,

Thanks for the correction. But I have written expression for

calculation CA from the beginning of the year till yesterday

Re: Calculate an average for the last 4 weeks

Wed, 02 Mar 2016 13:07:49 GMT

expression for calculating CA average for the last 4 weeks. starting from today()-2

Sum({1< DateTest={">=$(=Date(Weekstart(Today()-2,-4),'DD/MM/YYYY')) <=$(=Date(Today()-2,'DD/MM/YYYY'))"} >}

[CA])