Set analysis with if statement and getfieldselection

[#1]

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

[MayilVahanan]

HI

Try like this

=Sum({<Date = {'>=$(=MonthStart(AddMonths(Max(Date),-1)))<=$(=MonthEnd(AddMonths(Max(Date),-1)))'},detailYear=,detailMonth=>}Sales)

Hope it helps

[#3]

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.

[#4 · Accepted solution]

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))