topic Re: Average Weekly in QlikView

Average Weekly

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

How can I get in a Straight table weekly average from a daily observation?

so from

I started with a cross table but then got stuck...

Many thanks

Raphael

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:00:08 GMT

I meant not weekly average but rather 5 days average.

Re: Average Weekly

sunny_talwar — Wed, 11 Apr 2018 18:03:29 GMT

How are you coming up with 90 for Dan on 07/03/2018?

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:11:02 GMT

07/03/2018 only 2 data in past so average of 80 and 100 = 90

14/03/2018 we have full 5 data points so average of 100,40,80,100,40 = 72

thanks

Re: Average Weekly

sunny_talwar — Wed, 11 Apr 2018 18:13:54 GMT

Try this

Dimension

=WeekEnd(Dates, 0, 3)

Name

Expression

Avg(Grade)

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:16:45 GMT

Amazing!

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:25:03 GMT

Sunny,

My requirement actually sometime may need to be instead of 5 day 9 days or 13 day or something else. I don't think that with WeekEnd I will be able to adjust, is there any other way that the '5' will be in the formula? thank you

Re: Average Weekly

sunny_talwar — Wed, 11 Apr 2018 18:26:51 GMT

You can do that, but against which date in the dimension would you want to group them? I mean how did you know that 07/03/2018 is the date which you want to see and not 06/03/2018?

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:28:20 GMT

because I go by last date - which saw 11/04/2018 the this date + 4 dates above average and so on

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:41:15 GMT

Guess best to do that in the load script itself rather than in the dimension?

What do you think?

Re: Average Weekly

sunny_talwar — Wed, 11 Apr 2018 18:47:24 GMT

May be... but I am not sure how you will do this with different options you want your user to have... 5, 9 or 13?

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 18:50:44 GMT

I'll have to redo the load each time I want to change my parameter...

I tried to do it via loading script - to reduce my data and make it 5 days average but did not manage though

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 19:15:40 GMT

Hello Raphael,

First you would have to create a Week field, to do so you can simply use the Week function: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/week.htm

Once you've done that you would simply calculate the average grade per week per student using the Group By function in the back end: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptRegularStatements/Load.htm

OR you can also you the AGGR function in the front end: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/aggr.htm

The Group By option is probably the simplest, that is the option I used in the example attached. I just used that default week function, but can customize that to your needs by following the guide linked above.

And in case you are not able to open the QVW file, here is the load script I use:

// ORIGINAL SCRIPT

Source:

LOAD * INLINE [

Dates, Dan, Mark, Sophie

06/03/2018, 100, 10, 100

07/03/2018, 80, 20, 95

08/03/2018, 40, 30, 90

09/03/2018, 100, 40, 85

12/03/2018, 80, 50, 80

13/03/2018, 40, 60, 75

14/03/2018, 100, 70, 70

15/03/2018, 80, 80, 65

16/03/2018, 40, 90, 60

19/03/2018, 100, 100, 55

20/03/2018, 80, 10, 50

21/03/2018, 40, 20, 45

22/03/2018, 100, 30, 40

23/03/2018, 80, 40, 35

26/03/2018, 40, 50, 30

27/03/2018, 100, 60, 25

28/03/2018, 80, 70, 20

29/03/2018, 40, 80, 15

30/03/2018, 100, 90, 10

02/04/2018, 80, 100, 5

03/04/2018, 40, 10, 0

04/04/2018, 100, 20, 5

05/04/2018, 80, 30, 10

06/04/2018, 40, 40, 15

09/04/2018, 100, 50, 20

10/04/2018, 80, 60, 30

11/04/2018, 40, 70, 35

];

MyData:

CrossTable(Name,Grade)

Load * Resident Source;

drop Table Source;

// END OF ORIGINAL SCRIPT

JOIN(MyData)

LOAD Dates,

Week(Dates) AS Week

Resident MyData;

LEFT JOIN(MyData)

LOAD Week,

Name,

AVG(Grade) AS AvgGrade

Resident MyData Group By Week,Name;

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 19:22:17 GMT

Great idea!

How would I deal with with different year, so if more that one year in my observation - the week will be wrong,no?

Re: Average Weekly

Anonymous — Wed, 11 Apr 2018 21:11:55 GMT

In that case, you can create a Year dimension as well and added that to the Group By statement.

Or it's probably the easiest in the front end if you create a dimension such as YearWeek (ex. 2018-W1) using the DUAL function: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/FormattingFunctions/Dual.htm

Dual(WeekYear(Dates) & '-W' & Week(Dates), WeekStart(Date)) as YearWeek

Re: Average Weekly

Anonymous — Thu, 12 Apr 2018 06:37:48 GMT

Many thanks