set analysis for year to date, based on a numeric fiscal period

[#1]

i want to select jan, feb, mar (month) etc, but have some set analysis for ytd values based on a numeric fiscal period, where feb =01, mar=02 etc. All works well except for jan/period 12. When I have my set analysis based on month, it doesn't work for year to date for jan because qlikview sees jan as period 01.

I can get the ytd value to work if i select based on fiscal period (and have my set analysis based on fiscal period, but the users prefer to select calendar month.

This is the set analysis -

= ' Total Period Income: ' & num(sum({$<[Year] = {$(=max([Year]))}, FiscalMonth = {"<=$(=max({<[Year]={$(=max([Year]))}>} [FiscalMonth]))>$(=max({<[Year]={$(=max([Year]))}>} [FiscalMonth])-1)"}>} [Actual Amount]),'£#,##0')&' | '&

'Total YTD Income: ' & num(sum({$<[Year] = {$(=max([Year]))}, FiscalMonth = {"<=$(=max({<[Year]={$(=max([Year]))}>} [FiscalMonth])) >$(=max({<[Year]={$(=max([Year]))}>} $(vYearStartMonth))) "} [Actual Amount]),'£#,##0')&' | '

Any ideas would be great

thanks

[Miguel_Angel_Baeyens]

Hello,

You can create a new field in your calendar table using something like

InYearToDate(DateField, DateStartFY, 0, 2) AS FYFlag

This function will return -1 (true) when the DateField is within date in DateStartFY, for the current year, starting the calendar in February (month 2 for natural calendar, month 1 for your fiscal calendar).

Then you can use this flag in your set analysis regardless the field used by user to select.

Hope that helps

[#3]

Thanks miguel

I solved it by setting " Month = , " in the set analysis; this meant that qlikview ignored the selection in calendar month and just used the other set analysis for Fiscal Month to select by month.