Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
if your Period is always (at it seems from your table) on 1st of month
sum(Qty)
avg(aggr(sum(Qty),Period,Country))
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!
if your Period is always (at it seems from your table) on 1st of month
sum(Qty)
avg(aggr(sum(Qty),Period,Country))
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.
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.
Also a correct answer. instead of inline col, i added the two cols in the input data