Last 6 months data

[Anonymous]

Dear All

I have a requirement where I need to calculate average of last 6 months sales.

Example:  If we are in December we need to calculate average from June to November..

In Jan,  from July to Dec and so on.    Kindly help to achieve this possibility

Thanks & Regards

Chintan Gala

[veeranj]

Hi Chintan,

You can use Rangesum function  like rangesum(sum(sales),0,6) where as Month is your dimension .

Thanks,

Veera

[PrashantSangle]

Hi,

Use rangeavg()

Regards

[Anonymous]

Or is it better to show running average by taking last 180 days from today?

Thanks & Regards

Chintan Gala

[PrashantSangle]

not necessary 6 months = 180 days

Regards,

[vinieme12]

where are you trying to show this info Textbox? Straight Table (Rolling Average) ? or script?

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = NUM(year(Today())) & NUM(month(Today()),'00')

and vEndYearMonth = Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6)

Just ensure your Month variables are '00'  format meaning Jan is '01' and not '1'

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth)<$(vEndYearMonth)"} >} Sales)

with YearMonth you will be compare months over different year example 201611 to 201704

[Anonymous]

Thanks Vineeth. I will try the same and let you know.

I am trying to do this in front end, probably pivot table or straight table.

Thanks & Regards

Chintan Gala

[Anonymous]

Dear Vineeth

The vEndYearMonth   variable is not giving 6 month previous YearMonth.  It gives a junk date as 26-09-2654 something. Can you please suggest if any modification is required?

Thanks & Regards

Chintan Gala

[parimikittu]

Hi Chintan, It is always better to create a numeric value(extra column) for each month for whole master calendar. for example month,year,id This id has a numeric value. Month_numeric: Load Distinct     Month,     Year,     Rowno()+100 as Mon_num resident MasterCalendar Join the above with Master Calendar. SUM ( {< Month = {"=$(=Max(Mon_num)-7)"} >} Sales)

[Anonymous]

Dear Vj

Thanks for your reply. I will try the same and let u know if I am able to achieve the required result.

Thanks & Regards

Chintan Gala