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')

[sunny_talwar]

Would you be able to share a sample to check this?

[satishkurra]

Attached sunny.

Also i scrambled the data.

You can refer Sheet 3 for what we are trying.

And out of this question, i asked Fractile. You can see the Dashboard tab which has the scatter plots and we need to show some better distribution like what we are talking in different thread.

[sunny_talwar · Accepted solution]

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 {<Investment = {

}>} Fiscal_YearMonth)"}>}Investment),'#,##0')

[satishkurra]

Thanks Sunny