Last 6 month for current year and Last year

[Shahzad_Ahsan]

Hi 

I have 2 fields "CreationDate" and "Sales". 

I need the sum of sales for last 6 months, excluding current month, that means Oct is current month. I need to calculate from Apr to Sep and same Apr to Sep for last year also. 

What would be set analysis expression?

[rathore01]

Hi There,

First you need to create fiscal calendar from your CreationDate and the need to refer FiscalMonth and FiscalYear fields in set expression like below - 
Previous Year Apr - Sep  

sum({$<FiscalYear  = {"$(=(max(FiscalYear))-1)"} , FiscalMonth = -  {"$(=month(today()))"} >} Sales)

Current Year Apr - Sep  

sum({$<FiscalYear  = {"$(=Max(FiscalYear  ))"}, FiscalMonth =- {"$(=month(today()))"} >} Sales)

Thanks,

Rahul R

[vunguyenq89 · Accepted solution]

Hi,

Try these expressions

Last 6 months current year (Apr-Sep 2019)

=Sum({<CreationDate={">=$(=AddMonths(MonthStart(Today()),-6)) <$(=MonthStart(Today()))"}>} Sales)

Last 6 months last year (Apr-Sep 2018)

=Sum({<CreationDate={">=$(=AddMonths(MonthStart(AddYears(Today(),-1)),-6)) <$(=MonthStart(AddYears(Today(),-1)))"}>} Sales)

[Shahzad_Ahsan]

Hi 

This is working perfectly fine.

Thanks  

[Mogu]

Hi

these two work perfectly by themselves, but I want to try

Last 6 months current year (Apr-Sep 2019)/Last 6 months last year (Apr-Sep 2018)

to show like Sep19/Sep 18 in growth rate, any idea? Many thanks