Hello,
our customer wants to have summary line for each product . the line should have :
productid, product_name, current month total (curr_month), avg of last 2 months (avg month), curr_month/avg month.(avg column)

should be simple
but i tried almost every guide out there and it seem i need to use 1 master query and 2 sub append queries . then do the calculation "by hand" with calculated fields.

i want to ask if there is a simpler way .
yellowfin has many advanced calculation - moving average / moving totals
but for using them in one line i need a date column .

1. i can't figure out how to use those periods back - as months .
when i format the field like yyyymm it does not consider the column as date field.
so how do i use those calculation at year / month level (days is working)

2. i am trying to take the current date as month and then create an advance calculation in order to get the current month total.

3. also to make a moving avg of 2 periods (months) for the "avg column"

4. if i will filter only the current months - the advance calculation will still make the calculation of moving avg - 2 periods back?

5. can i make a pivot table with months at columns and calculate a new columns out of part of the months i get at the columns? like
if i have:
jan | feb | march
can i add a column that adds feb + march?
or i need to calculate them one by one ?

I think you should consider adding a new "advance calculation" that
gets parameters of date and period back (day / month / year)
and return the aggregate for that.
so i can ask for the sum of 3 periods ago
that way . i could show the period 0,1,2 and calculate the avg
with out sub query

thanks

itamar