BAQ Convert Fiscal Period to Months

Epicor ERP 10
1

I’m able to use an IIF statement to convert the fiscal periods to months (i.e. January, etc.) however the BAQ only allows a nested IIF statement of 10 levels. Is there a workaround to convert all 12 fiscal periods to their 12 months?

IIF(GLJrnDtl.FiscalPeriod = 1, ‘January’, IIF(GLJrnDtl.FiscalPeriod = 2, ‘February’,…

2

Use substring and trim like:

TRIM(SUBSTRING( 'January February March April ...', (FP-1) * 10 + 1, 10))

The string is each month padded to 10 characters. January takes locations 1-10, February 11-20, and so on.

Edit

I’m sure there are better ways…

3 
DateName( month , DateAdd( month , GLJrnDtl.FiscalPeriod , -1 ))
2 Likes
4

I was going to go that route first, but couldn’t remember the exact syntax for the DateAdd()

Edit

Nice use of -1 for the date to add to.

Does DateAdd(year, 1, ‘03/01/2023’) give the date of 3/1/2024 or 2/29/2024?

5

I just need the actual Month so if Fiscal Period = 1 then it should equal “January” and not the actual date format. Does that expression return the actual month of “January”? I’m using that value as a tracker filter in the dashboard so rather than use 1, the users can select “January”.

6

Correct it returns January

8

Why not using a case ?
case
when 1 then ‘january’
when 2 then ‘February’
…
end

?

1 Like