Aggr and rangeavg - how to?

[hellmann]

Hello,

I häve been chewing for a few days on a seemingly simple task, that I cannot mange to complete. I would very much appreciate your help in solving this.

Here is the situation. I have a data with the following fields:

orderid, orderdate, product

What i would like to end up with is a table consisting of two columns:

a) product

b) average of orders occurred in the past 6 months in which an order actually occurred, i.e. I would like to avarage over the last three months with non-zero orders.

Hat        

Coat

Sweater

So, here is what I came up with so far.

1. Aggregate by product and month

Aggr( Count (orderid),[orderdate.autoCalendar.YearMonth], product)

2. Then I believe I need to to a rangeavg like:

Rangeavg( below (aggr( Count(ID),[Date.autoCalendar.YearMonth]),0,3))

The aggregation works as i expect, see screenshot:

I can also apply an avarage, like:

avg(

          aggr(

                    Count(ID),[Date.autoCalendar.YearMonth],Product)

          )

But with rangeavg everything falls apart. Taking the data from the first screenshot, I would like to end up with a table like:

Product      Rangeavg (below()....0,3), meaning that I want to the average over the last three months for each product.

Coat               2                    (average over Apr, Mrz, Feb)

Hat                   2,67          (average over Apr, Mrz, Jan)

Sweater          2                  (average over Aug, Jul, Jun)

I have a hunch that all it needs is to re-order aggr, rangevg etc, but no matter what I try i do not get the results I expect. It must be a simple mistake.

Best regards,

Harald

[Anil_Babu_Samineni]

May be RangeAvg need two parameters. Try with this?

Rangeavg( below (aggr( Count(ID),[Date.autoCalendar.YearMonth]),0,3),1)

[sunny_talwar]

I would like to avarage over the last three months with non-zero orders.

How do you determine if an order is non-zero? Why is DL 100 not included in your Products? Seems to have been sold on 4/4/2017 and 5/5/2017 (which is within 3 months of 6/1/2017 and even today, if this is based on today)

[hellmann]

No, this is not the error. The paramaters for rangeavg are wrapped into the below() statement.

[hellmann]

Sorry, the DL 100 can be ignored, I have attched a corrected version.

My way of thinking is this: if I aggregate by YearMonth, I will get combinations of lines containing only months where I have orders. by applying rangeavg over those months, I will average over non-zero values only.

[sunny_talwar]

I sort of understand what you want, but it might help if you are able to provide the final desired output to help you better

[sunny_talwar · Accepted solution]

How about this

RangeAvg(

FirstSortedValue(Aggr(Count(orderid),[orderdate.autoCalendar.YearMonth], product), -Aggr([orderdate.autoCalendar.YearMonth],[orderdate.autoCalendar.YearMonth], product)),

FirstSortedValue(Aggr(Count(orderid),[orderdate.autoCalendar.YearMonth], product), -Aggr([orderdate.autoCalendar.YearMonth],[orderdate.autoCalendar.YearMonth], product), 2),

FirstSortedValue(Aggr(Count(orderid),[orderdate.autoCalendar.YearMonth], product), -Aggr([orderdate.autoCalendar.YearMonth],[orderdate.autoCalendar.YearMonth], product), 3)

)

Where firstsortedvalue will give you the value associated with max orderdate for each of the product and firstsortedvalue(,2) will give value associated with second max orderdate and firstsortedvalue(,3) will give the value associated with third max orderdate.

[hellmann]

Hi Sonny,

thanks a lot for the hint, it works!

The approach is very understandable for me. I was a bit too ambitious, I think, when aiming for a solution that would give me flexibility in terms of the number of months used for averaging. Your solution requires a new line to be added for each additional month.

I think I still do not have the corrct menatl image of the aggr function. my initial:

Rangeavg(above(Aggr(Count(ID),[Date.autoCalendar.YearMonth], Product),0,3))

actually works correctly if I include another dimension (Date.autocalendar,YearMonth) in my table, as seen in the screenshot below. The red values are the same as the one obtained by your solution (table on the right hand side).

So I think, with Rangeavg(above(Aggr(Count(ID),[Date.autoCalendar.YearMonth], Product),0,3))

I obtain a table with two dimensions (Product, Date), and as long as i include both dimensions I will not run into a granularity problem.

So, then I thought, I will just apply your FirstSortedValue to my rangeavg., like:

Firstsortedvalue(Rangeavg(above(Aggr(Count(ID),[Date.autoCalendar.YearMonth], Product),0,3)),

                              - Rangeavg(above(Aggr(Count(ID),[Date.autoCalendar.YearMonth], Product),0,3))

                              )

But this formula is wrong...

Thanks again for helping out!