topic Re: Previous Year in QlikView

Previous Year

jjking58 — Wed, 25 Nov 2020 16:16:04 GMT

I've tried all ways to produce a straight table for a previous year using a fiscal Calendar. I have been able to create a current year table with the help of the Qlikview community which works perfectly. Can someone show me what i'm doing wrong.

Thank you in advance.

Jim

Here is my Fiscal Calendar and load script for the file driving the calendar All i need is a set analysis expression to retrieve the previous year amount.

Here is the expression i'm using to select the previous data

sum({<IHIDAT = {">=$(AddYears(FYearStart,-1)<=$(FYearStart)"}>}Amount)

Re: Previous Year

sunny_talwar — Wed, 28 Dec 2016 20:49:31 GMT

What is FYearStart a variable? How is it defined? May be try this:

Sum({<IHIDAT = {">=$(=Date(AddYears(FYearStart,-1), 'M/D/YYYY'))<=$(=Date(FYearStart, 'M/D/YYYY'))"}>}Amount)

Re: Previous Year

jjking58 — Wed, 28 Dec 2016 20:53:55 GMT

I tried your suggestion and show no data

FYearStart comes from the Fiscal Calendar script. Should I be comparing IHIDAT which is the invoice date from the table and the field used to create the Fiscal Calendar.

Re: Previous Year

sunny_talwar — Wed, 28 Dec 2016 22:00:11 GMT

Can you just do this?

Sum({<FYearStart= {"$(=Date(Max(FYearStart)))"}>}Amount)

Re: Previous Year

alan_richmond — Wed, 28 Dec 2016 22:46:17 GMT

these are the variable I use

sLYear	=sTYear-1
sTYear	=Year

mTYSalesVal	Num(Sum(SalesValue * $(vIncludeTrailers))/vRound,'$#,##0')
mLYSalesVal	Num(Sum({$<Year={$(sLYear)}>} SalesValue * $(vIncludeTrailers))/vRound,'$#,##0')

Re: Previous Year

jjking58 — Wed, 28 Dec 2016 23:22:05 GMT

That gives me the current year.

Re: Previous Year

jjking58 — Wed, 28 Dec 2016 23:29:24 GMT

when i display the contents of IHIDAT it only shows the dates from the selection made ex.(2014/2015) excludes all 2016 data. Would this cause the previous or current years data from be processed?

Re: Previous Year

sunny_talwar — Thu, 29 Dec 2016 02:25:22 GMT

May be this:

Sum({<FYearStart= {"$(=Date(AddYears(Max(FYearStart), -1)))"}, IHIDAT>}Amount)

Make sure to ignore selections in other date related fields where you plan to make selections. I have added IHIDAT, you can add others the same way

Re: Previous Year

jjking58 — Thu, 29 Dec 2016 03:11:27 GMT

Sunny,

Still no luck. Displays no data. I've included a screen shot showing the selection of 2015/2016 the IHIDAT only includes dates 20150401- 20160331 (April is the start of fiscal period). There are no records in IHIDAT for 20140401- 2015331. Could this be why the above statement does not work? Is there a way around this and include all records between 2013-2017

Thanks for spending so much time on this.