Variable to determine max Fiscal month

[#1]

In my detail/fact records, I have a mapping for FiscalYr, and FiscalMonth based on the invoice date (our FY is 11/1 to Oct31) 

• To map the Fiscal Month and Year  I create a formula from the invoice date to get MMYY  //  =Month(InvoiceDate)&Right(Year(InvoiceDate),2)
• I then have table in Excel I Left Join to the fact table to assign a transaction a FYear and FMonth -I am doing this because I use vCurFisYr and vCurFisMo to create my MTD and YTD values.
• I then set a variable to get Current Fiscal Year     // vCurFisYr=max(FiscalYear)

My question is how do I do this to get the current fiscal month in the current fiscal year.    I cannot simply say vCurFisMo=max(FisMo) or it will be 12 every time.

I would like in my script to say something like this:  LET vCurFisMo=max(FiscalMonth) WHERE FiscalYear=vCurFisYr

So I do set analysis on vCurFisYr and vCurFisMo  for my MTD and YTD calcs

[JonnyPoole · Accepted solution]

how about a max with an IF() to filter down the FiscalMonths ? 

max ( if( FiscalYear=vCurFisYr,FiscalMonth) )

[mukesh24]

Hi,

Use following

vCurFisYr = max(FiscalYear);

vCurFisMo = max({<FiscalYear = {"$(vCurFisYr)"}>}FiscalMonth);

[#4]

Both of these equations worked but I was unable to get them to work within my script.

Let vCurFisYr=max(FisYr);

Let vCurFisMo=max ( if( FisYr=vCurFisYr,FisMo) ) ;

How I did get it to work though is by setting a Trigger on my Document Properties

• On Open - External/Set Variable - vCurFisYr = max(FisYr)
• On Variable Input (vCurFisYr) - External/Set Variable - vCurFisMo=max ( if( FisYr=vCurFisYr,FisMo) )

So I wanted to take it a step further but am just getting a - returned.   Each FisMo has a MonthName so I thought I could apply the same logic, but it is not working

• On Variable Input (vCurFisMo) - External/Set Variable - vMonthName=If(FisMo=vCurFisMo, MonthName)

[JonnyPoole]

try using a 'set' rather than a 'let' when defining the variables in the script. 

Then use $() expansion when you invoke the variable in the UI.