topic Re: Last Fiscal Year comparison in QlikView

Last Fiscal Year comparison

Wed, 10 Dec 2014 00:08:06 GMT

Hi All,

I just started to build reports in Qlikview and seek some help for the following.

I encountered a small issue while trying to do Year on Year analysis in Qlikview.

I want to show column for June in the previous financial year (i.e June 2014 for 2013/2014 fin year).

I tried to use standard approach which is

=Sum ({$<FY= {$ (=Max (FY)-1)}, Month = {June} >} Amount)).

However, QlikView does not seem to recognize my FY field as numerical. It works with max (FY) but not with max(FY)-1.

I do not have a master calendar. Here are all my date-related fields in the load script.

Month(Date) as Month,

Year(Date) as Year,

quarterName(Date) as Quarter,

Date(Monthstart(Date), 'MMM-YYYY') as YearMonth,

Date(yearstart(Date,0,7),'YYYY') as FY,

dual(year(addmonths(Date, 1-$(vFirstMonth))) & '/' & right(year(addmonths(Date, 1-$(vFirstMonth)))+1,2), year(addmonths(Date, 1-$(vFirstMonth)))) AS DateFieldFiscalYear,

dual('Quarter ' & ceil(month(addmonths(Date, 1-$(vFirstMonth)))/3), ceil(month(addmonths(Date, 1-$(vFirstMonth)))/3)) AS DateFieldFiscalQuarter,

num(month(addmonths(Date, 1-$(vFirstMonth))))AS DateFieldFiscalMonth,

Thanks a lot in advance,

Chris

Re: Last Fiscal Year comparison

Wed, 10 Dec 2014 00:08:57 GMT

Fiscal Year

That should help!!

Re: Last Fiscal Year comparison

Wed, 10 Dec 2014 01:01:50 GMT

Thanks Ram,

I used the model by Henric. However when I try to get June previous year values, it doesnt seem to work.

Here is my set expression.

=sum({$<fyear = {$(=Max(fyear)-1)}, fmonth={"6"}, YearMonth = , Quarter=, Year =, Month =, FMonth =, FYear =, Date = >} Amount/1000)

Am I doing something wrong?

Chris

Re: Last Fiscal Year comparison

jagan — Wed, 10 Dec 2014 03:03:36 GMT

Hi,

Try like this

=sum({$<fmonth={"6"}, YearMonth = , Quarter=, Year ={$(=Max(Year)), $(=Max(Year) - 1)}, Month =, FMonth =, FYear =, Date = >} Amount/1000)

The above expression gives the 6 th month Amount for 2014 and 2013.

If you want previous year then use this

=sum({$<fmonth={"6"}, YearMonth = , Quarter=, Year ={$(=Max(Year) - 1)}, Month =, FMonth =, FYear =, Date = >} Amount/1000)

Regards,

Jagan.