Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I apologize, I'm still learning set analysis. We have a field that is calculating the prior month units based on the year/month selected. Unfortunately this doesn't work when January is selected as the months are numeric so it sees month 1 and then subtracts 1 to get prior month, which doesn't work as there is no month zero. I need to modify the following formula to include if statements for both the year and the month. If the dteailmonth is >1 then use the detailmonth, else use detailmonth 12. And if the detailmonth=1 then the year needs to be detailyear-1.
Does anyone know how I can modify this to accomodate those if statements?
sum({$<detailmonth={"$(=GetFieldSelections(detailmonth)-1)"},detailyear = {"$(=GetFieldSelections(detailyear))"} >} units)
Thanks in advance!!~ Rebecca
I ended up doing this and it seems to be working fine.
if(detailmonth>1,sum({$<detailmonth={"$(=GetFieldSelections(detailmonth)-1)"},detailyear = {"$(=GetFieldSelections(detailyear))"} >} units),
sum({$<detailmonth={12},detailyear = {"$(=GetFieldSelections(detailyear)-1)"} >} units))
HI
Try like this
=Sum({<Date = {'>=$(=MonthStart(AddMonths(Max(Date),-1)))<=$(=MonthEnd(AddMonths(Max(Date),-1)))'},detailYear=,detailMonth=>}Sales)
Hope it helps
That doesn't quite work. I think it's because my data coming in is already parsed into months 1-12, and years. I don't actually have a Date field.
I ended up doing this and it seems to be working fine.
if(detailmonth>1,sum({$<detailmonth={"$(=GetFieldSelections(detailmonth)-1)"},detailyear = {"$(=GetFieldSelections(detailyear))"} >} units),
sum({$<detailmonth={12},detailyear = {"$(=GetFieldSelections(detailyear)-1)"} >} units))