Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregating weekly loaded data as monthly, etc

Hi all,

I've been going in circles with this for hours now so would appreciate some help.

What are generic methods one can use to aggregate lower timeframe data fields based on time/date dimensions. I have tried set analysis but not coming right. Also functions like aggr, rangesum, rangeavg with count(distinct and am not getting anything workable syntax wise that returns correct values.

In the data below you'll see I have data recorded every Friday (its the % absenteeism in a particular department for the week).

weeklyasmonthly.png

When I plot this data the line is too jiggly so I want to take this data and smooth it by plotting the monthly average instead given that I have the weekly averages available, I was hoping to identify and average the data which belongs to a particular month and then just obtain that value. e.g. below I have highlighed in blue the data I want to average for January, I'd like to average the for Feb, Mar, etc as well and plot as monthly data from this.

For example for the Production column for 01/2012 (MM/YYYY format)  I'd expect to Avg(4.45, 2.08, 2.87) and plot that as the Jan value. Similarly for 02/2012 I'd expect to Avg(2.87, 2.68, 2.17, 2.39) and plot that as the Feb value ... etc for the rest of the year's months.

How can I effect this? I've been trying to do this as a chart expression with [CalendarWeekEndingFriDate] as my dimension but I'll try any approach that works. Thanks

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Arrive Month and Year dimensions as suggested by Stefan. 

=monthname( CalendarWeekEndingFriDate )

Now add this new Month dimension as dimension in chart and use the following expression

= avg(aggr(Sum(ProductionColumn), CalendarWeekEndingFriDate))

Hope this helps you.

Regards,

Jagan.


View solution in original post

3 Replies
swuehl
MVP
MVP

According to your definition of assigning CalendarWeekEndingFriDate to months, I think you just need to create the month from these dates. For example, you can create a calculated dimension

=monthname( CalendarWeekEndingFriDate )

and then use as expression something like

= avg(ProductionColumn)

If this results in the expected values, I would recommend adding a new field that calculates the months from your date field in the load script

LOAD

...

CalendarWeekEndingFriDate,

monthname(CalendarWeekEndingFriDate) as YearMonth,

ProductionColumn,

...

from ...;

Hope this helps,

Stefan

edit: and to aggregate to year level, just use year() function.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Arrive Month and Year dimensions as suggested by Stefan. 

=monthname( CalendarWeekEndingFriDate )

Now add this new Month dimension as dimension in chart and use the following expression

= avg(aggr(Sum(ProductionColumn), CalendarWeekEndingFriDate))

Hope this helps you.

Regards,

Jagan.


Not applicable
Author

Awesome, thanks very much Stefan and Jagan,

Stefan using your suggestion I can do a monthly chart dimension, and you're right Jagan to actually get a value to plot I need to use your aggr formula syntax.

Given my input data I now get as output this, which is great.

newdim-moaggr.png

Hmmm, but now this brings one more question.

Using

LOAD MonthName(CalendarWeekEndingFriDate) as CalendarMonthYear

as the scripted dimension

and then 

avg(aggr(Sum(CuttingAbsenteeismPercent), CalendarWeekEndingFriDate))

as the expression I get the above result.

As you can see I am getting no dimension summarised values output for April 2012.

Am guessing its because the month is still incomplete / presence of null values, but how can I handle this aspect so that for incomplete periods such as April 2012 now, I can get a "MonthToDate" value to plot?

Thanks again,

------------

Stop Press. Ignore the above. It actually works fine - also for April. When I tested with just one ProductionColumn, I'm sure I didnt have any date filters active and it wouldnt show me April data hence I was confused. I just added further production columns to the chart using the same aggr expression and April has appeared, so thanks, everything working 100% now.

Message was edited by: manoangazi