Last Fiscal Year Month (QlikView)

[satishkurra]

Hi

I have a requirement to display the chart for Revenue of Last Fiscal Year Month every year

Data i have is till 2018-06

I need to display chart for 2017-09, 2016-09, 2015-09... (09 is Septemeber which is the max fiscal month in every completed year. In 2018, we have data only till 2018-06 where 06 is March)

If i perform Max(FiscalYearMonth) i get 2018-06 and 2017-06...

I need to display the chart for Last Fiscal Year Month excluding 2018 in that way the chart should display 2017-09,2016-09....

Can someone please help on the same.

Currently I'm using below expression which gives only for 2017-09

=Num(Sum({<Date={'<=$(=YearEnd(Today(),-1,10))'},Fiscal_YearMonth={'$(=max({<FiscalYear = {"$(vMaxLastYear)"}>}Fiscal_YearMonth))'}>}Revenue),'#,##0')

[satishkurra]

ANy one?

[sunny_talwar]

Try this

=Num(Sum({<Date, Fiscal_YearMonth = {"=SetDateYear(Fiscal_YearMonth, Year(Today()) = SetDateYear(Max(TOTAL {<FiscalYear = {"$(vMaxLastYear)"}>} Fiscal_YearMonth), Year(Today())) or Fiscal_YearMonth = Max(TOTAL Fiscal_YearMonth)"}>}Revenue),'#,##0')

[satishkurra]

Hey Sunny

What is SetDateYear in this expression?

Thanks

Satish

[sunny_talwar]

Its a QlikView function.. what's wrong?

[satishkurra]

Attached

[sunny_talwar]

Try now

=Num(Sum({<Date, Fiscal_YearMonth = {"=SetDateYear(Fiscal_YearMonth, Year(Today())) =SetDateYear(Max(TOTAL {<FiscalYear = {"$(vMaxLastYear)"}>} Fiscal_YearMonth), Year(Today())) or Fiscal_YearMonth = Max(TOTAL Fiscal_YearMonth)"}>}Revenue),'#,##0')

[satishkurra]

Same error

[sunny_talwar]

Can you share the image of the expression in the expression editor?

[satishkurra]

Attached