How to achieve this

[#1]

My data is has following cols

Country, State, Period(in mm/dd/yyyy format), Qty

A, A1, 1/1/2015, 10

A, A2, 1/1/2015, 20

A, A1, 2/1/2015, 25

A, A1, 1/1/2014, 15

A, A1, 2/1/2014, 10

B, B1, 3/1/2015, 30

I want to achieve the following output

Year(Period)     Country     Sum(Qty) (for the Year)     Average Qty of each but the based on MONTHLY aggregation

2015                   A           10+20+25=55                    Average of (10+20) and 25 = 55/2

2015                   B            30                                   30/1 = 30

2014                   A            15+10=25                         Average of 15 and 10 = 25/2

I assume Aggr will have to be put to use - tried few options but gettig "-" as the answer.

In addition to Average, I also want to get the Standard Deviation.

Any help much appreciated.

Thanks,

Gopal

[oknotsen]

I suggest adding a Year and a Month field to your data model based on that Period field.

Assuming you did that, you are going to make a Table with 2 dimensions:

- Year

- Country

You are going to create two expressions:

- sum(Qty)

- avg(aggr(sum(Qty), Year, Month))

Good luck!

[maxgro · Accepted solution]

if your Period is always (at it seems from your table) on 1st of month

sum(Qty)

avg(aggr(sum(Qty),Period,Country))

[Anonymous]

Here's a non-aggr solution:

ExampleData:

Load

    *,

    Year (PeriodDate) as PeriodYear,

    Year (PeriodDate) & '-' & Month(PeriodDate) as PeriodMonth

Inline

[

Country, State, PeriodDate, Qty

A, A1, 1/10/2015, 10

A, A2, 1/10/2015, 20

A, A1, 2/10/2015, 25

A, A1, 1/10/2014, 15

A, A1, 2/10/2014, 10

B, B1, 3/10/2015, 30

];

In a straight table with country and state as dimensions, add two expressions:

sum(Qty)

sum (Qty) / count (distinct PeriodMonth)

For the standard deviation, easiest to use aggr:

sum(Aggr(stdev(Qty),PeriodYear,Country))

With Aggr, always make sure you have an aggregate function outside of the aggr. So you need:

AggregationFunction1(Aggr(AggregationFunction2(field)))

Without the outside aggregation function you may receive a hyphen. Here a hyphen frequently represents more than one value. The outside aggregation makes sure you're returning only one value.

[#5]

Added two dimensions i.e. Year and Year-Month to the data as aggr doesn't seem to work on expressions and then applied the avg expression as suggested. it works.

[#6]

Also a correct answer. instead of inline col, i added the two cols in the input data