topic Re: Variable to determine max Fiscal month in QlikView

Variable to determine max Fiscal month

Tue, 03 Mar 2015 22:49:54 GMT

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

Re: Variable to determine max Fiscal month

JonnyPoole — Wed, 04 Mar 2015 02:27:35 GMT

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

max ( if( FiscalYear=vCurFisYr,FiscalMonth) )

Re: Variable to determine max Fiscal month

mukesh24 — Wed, 04 Mar 2015 04:17:53 GMT

Hi,

Use following

vCurFisYr = max(FiscalYear);

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

Re: Variable to determine max Fiscal month

Wed, 04 Mar 2015 17:43:40 GMT

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)

Re: Variable to determine max Fiscal month

JonnyPoole — Wed, 04 Mar 2015 18:05:04 GMT

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.