topic Re: Last Fiscal Year Month (QlikView) in QlikView

Last Fiscal Year Month (QlikView)

satishkurra — Thu, 03 May 2018 20:24:21 GMT

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

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 01:30:57 GMT

ANy one?

Re: Last Fiscal Year Month (QlikView)

sunny_talwar — Fri, 04 May 2018 02:01:45 GMT

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

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 02:05:53 GMT

Hey Sunny

What is SetDateYear in this expression?

Thanks

Satish

Re: Last Fiscal Year Month (QlikView)

sunny_talwar — Fri, 04 May 2018 02:24:27 GMT

Its a QlikView function.. what's wrong?

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 02:42:05 GMT

Attached

Re: Last Fiscal Year Month (QlikView)

sunny_talwar — Fri, 04 May 2018 02:48:56 GMT

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

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 02:59:00 GMT

Same error

Re: Last Fiscal Year Month (QlikView)

sunny_talwar — Fri, 04 May 2018 03:00:36 GMT

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

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 03:04:48 GMT

Attached

Re: Last Fiscal Year Month (QlikView)

sunny_talwar — Fri, 04 May 2018 03:09:15 GMT

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

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 03:43:53 GMT

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.

Re: Last Fiscal Year Month (QlikView)

sunny_talwar — Fri, 04 May 2018 08:59:45 GMT

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

Re: Last Fiscal Year Month (QlikView)

satishkurra — Fri, 04 May 2018 13:26:27 GMT

Thanks Sunny