Aggregation per day of week

[robert_mika]

From this sample

CalendarDate	DayOfWeek	id
01/01/2015	Thu	500
01/01/2015	Thu	500
01/01/2015	Thu	750
01/01/2015	Thu	750
02/01/2015	Fri	223
02/01/2015	Fri	500
02/01/2015	Fri	500
02/01/2015	Fri	750
02/01/2015	Fri	750

to get

01/01/2015 Thu 2500

02/01/2015 Fri  2723

in line chart

[robert_mika]

Yes

Thanks Sunny

My aim was to take the average from this.

Your simple help me to get to the second part

count(DISTINCT(CalendarDate&'-'&DayOfWeek))

is the a way to use aggregation to get the average per week?

[sunny_talwar]

Average per week? Not sure what you mean? Can you point out with the expected output in your second sample?

[robert_mika]

Mon	3222
Tue	2250
Wed	2750
Thu	2506.5
Fri	2736.5
Sat	2500
Sun	2481

My model have null values so I need to ignore the day of the week where values are NULL

[sunny_talwar]

This seems to be working:

=Avg(Aggr(Sum(id), CalendarDate, DayOfWeek))

[robert_mika]

Yes for this data set.

What if your dates have missing values?

The aggregate does not ignore them taking NULL as values and add to count.

Any way to avoid that without using have IF statement.

[sunny_talwar]

I added two rows the data provided and used Null id for them and created a unique key using RowNo() function:

Table:

LOAD RowNo() as UniqueKey,

  CalendarDate,

  DayOfWeek,

  If(Len(Trim(id)) > 0, id) as id;

LOAD * Inline [

CalendarDate, DayOfWeek, id

01/01/2015, Thu, 500

01/01/2015, Thu, 500

01/01/2015, Thu, 750

01/01/2015, Thu, 750

02/01/2015, Fri, 223

02/01/2015, Fri, 500

02/01/2015, Fri, 500

02/01/2015, Fri, 750

02/01/2015, Fri, 750

03/01/2015, Sat, 250

03/01/2015, Sat, 500

03/01/2015, Sat, 750

03/01/2015, Sat, 1000

04/01/2015, Sun, 500

04/01/2015, Sun, 981

04/01/2015, Sun, 1000

05/01/2015, Mon, 250

05/01/2015, Mon, 500

05/01/2015, Mon, 1222

05/01/2015, Mon, 1250

06/01/2015, Tue, 250

06/01/2015, Tue, 1000

06/01/2015, Tue, 1000

07/01/2015, Wed, 250

07/01/2015, Wed, 500

07/01/2015, Wed, 1000

07/01/2015, Wed, 1000

08/01/2015, Thu, 2

08/01/2015, Thu, 3

08/01/2015, Thu, 4

08/01/2015, Thu, 4

08/01/2015, Thu, 500

08/01/2015, Thu, 750

08/01/2015, Thu, 1250

09/01/2015, Fri, 250

09/01/2015, Fri, 1250

09/01/2015, Fri, 1250

10/01/2015, Sat

11/01/2015, Sun

];

Expression:

=Avg(Aggr(Sum({<UniqueKey = {"=Len(Trim(id)) > 0"}>}id), CalendarDate, DayOfWeek))