topic Re: year on year comparison in QlikView

year on year comparison

rido1421 — Tue, 27 Jan 2015 07:48:53 GMT

Hi There

I have the below fields

[Invoiced Amount],

[Fiscal Year],

[Fiscal Month],

Date

I have two text boxes one for current Year and one for Previous Year

in my Current Year =Sum({<[Fiscal Year]={$(vMaxYear)}>}[Invoiced Amount]) /// This works fine

How would I do previous year so that it is only up until Todays date or Yesterday.

Ive used

If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD in the script which will give me current months current days flag

for each year.

any idea what I could do in my previous year text box with the above information...?

Thanks

Re: year on year comparison

Anonymous — Tue, 27 Jan 2015 08:02:22 GMT

Hi,

You can try like this for

previous year =Sum({<FY={$(=Max(FY)-1)}>}[Invoiced Amount])

Regards

Arun

Re: year on year comparison

rido1421 — Tue, 27 Jan 2015 08:04:37 GMT

Hi Arun

That will give me Sum for the total of last year...

I would need it to give me a comparison of last year to this year... YTD...

Re: year on year comparison

Anonymous — Tue, 27 Jan 2015 08:35:59 GMT

Hi,

Try

=Sum({<FY={$(=Max(FY)-1)},MonthOrder = {"<=$(= max({<FY={$(=max(FY))}>} MonthOrder))"}>}[Invoiced Amount])

Where MonthOrder is month no.

Regards

Arun

Re: year on year comparison

rido1421 — Tue, 27 Jan 2015 08:43:39 GMT

Hi Arun

it doesnt seem to work...

below is the expression I am currently using which does seem to work... but as you can imagine

it is resource intensive, I need would like to do it in the script to try and simplify the expression

it is making my model slow.

=(If(GetFieldSelections([Month Abbrev])= vCurrentMonth and GetSelectedCount([Day Number in Month])=1,

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

((if(

GetFieldSelections([Month Abbrev])= vCurrentMonth

or GetSelectedCount([Month Abbrev])<1

or GetSelectedCount([Day Number in Month])>1

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

num(sum({$< [Fiscal Year] = {$(vPREVYEAR)},[Month Abbrev]={$(vCurrentMonth)},[Day Number in Month]={"<=$(=(vCurrentDay))"}>} [Invoiced Amount])/1000)),'#,##0'))

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

Thanks