topic Year on Year Comparison in QlikView

Year on Year Comparison

rido1421 — Thu, 15 Jan 2015 05:16:37 GMT

Hi There

I am trying to create a year on year comparison on my dashboard

I have two text boxes one displaying current year sales and another displaying prev year sales

Current Year" =num(sum({$< [Fiscal Year] = {$(vMAXYEAR)},[Year-Month]={"<=$(=(vMAXYM))"}>} [Invoiced Amount])/1000,'#,##0')"

Previous Year="=vPREVYEAR&' : '&num(sum({$< [Fiscal Year] = {$(vPREVYEAR)},[Year-Month]={"<=$(=(vMAXLASTYEAR))"}>} [Invoiced Amount])/1000,'#,##0')"

The problem with the previous year is that it doesnt display MTD for the current month , so for example for 2014 it is displaying the full month of Jan comparing the two list boxes this is incorrect .

I have tried "If( Day(DATE_KEY) <= Day(Today()), 1, 0) as IsInMTD" in the script and then

using the flag in the set analysis expression as per below

"=vPREVYEAR&' : '&num(sum({$< [Fiscal Year] = {$(vPREVYEAR)},[Year-Month]={"<=$(=(vMAXLASTYEAR))"},IsInMTD={1}>} [Invoiced Amount])/1000,'#,##0')"

This doesnt give me correct figures... any idea what I could be doing wrong... or perhaps a simpler solution ?

I need to use the expression in a number of places so im trying to keep the expression as simple as possible as not to use up to much resources...

Thank you .

Re: Year on Year Comparison

Gysbert_Wassenaar — Thu, 15 Jan 2015 10:09:14 GMT

I don't see any obvious issue in what you posted. Can you post a document that demonstrates the problem?

Re: Year on Year Comparison

Colin-Albert — Thu, 15 Jan 2015 10:18:03 GMT

Add a test table box showing DATEKEY and IsInMTD to test that the flag is correct in each year.

Re: Year on Year Comparison

rido1421 — Tue, 27 Jan 2015 09:39:12 GMT

Hi Gysbert

My problem here seems to be that "If( Day(DATE_KEY) <= Day(Today()), 1, 0) as IsInMTD only gives

the current month as a flag, I would need the previous months of the fiscal year + the amount of days in the current month

Sum({<IsInMTD={1},[Fiscal Year]={$(vPREVYEAR)}>}[Invoiced Amount])

will only give me the invoiced amount last years fiscal month equivalent to this year

so in this case this months fiscal month is 4 and the above expression only gives the invoiced amount for 2014 fiscal month 4(Which is the current month)

I need an accumulative figure fiscal month 1+2+3+4(up until todays date)

so that I can do the comparison ,current year vs Prev year

I hope my explanation is not to confusing

Thank you