Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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
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)
Hi
This is working perfectly fine.
Thanks
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