Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Chintan,
You can use Rangesum function like rangesum(sum(sales),0,6) where as Month is your dimension .
Thanks,
Veera
Hi,
Use rangeavg()
Regards
Or is it better to show running average by taking last 180 days from today?
Thanks & Regards
Chintan Gala
not necessary 6 months = 180 days
Regards,
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
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
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
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)
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