topic Re: Can't get Prior Year (YTD) to work in QlikView

Can't get Prior Year (YTD) to work

amber2000 — Wed, 05 Aug 2015 10:42:06 GMT

Hello everybody,

I've read a lot about YTD, MTD and I'm trying to implement this in my report.

I have 2 years of data(from jan 2014 untill now 2015)

In my KPI meter I want to show the sum of a value from today (05/08/2015) untill last year (05/08/2014)

YTD MaxDate is working great 😉

YTD PriorYearDate gives me 0's and shows no value at all.

Am I doing something wrong?

I attach my report so everyone can see what I mean.

Can anybody help me out please???

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

antoniotiman — Wed, 05 Aug 2015 10:57:31 GMT

Hi,

Your variable vPriorYearDate has format ' DD MMM YYY'

Calendar_Day has Fromat 'YYYYMMDD'

Change format of variable 'YYYYMMDD'

Regards

Re: Can't get Prior Year (YTD) to work

amber2000 — Wed, 05 Aug 2015 11:50:44 GMT

Hi Antonio

You are so wright, I've adjusted the format of vPriorYearDate to 'YYYYMMDD'.

So now I see data for every month of 2014 and the total of 2014, but 0's for 2015.

There must be another mistake somewhere.

kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

qlikmsg4u — Wed, 05 Aug 2015 11:59:12 GMT

try this expression and let us know

=Sum({$<Year=,Month=,Day=,Calendar_Day={'<=$(vPriorYearDate)'},MonthYear=,Bex_Action_Type ={FREINTE}>}[Bex_Value]) - sum({$<Year=,Month=,Day=,Calendar_Day={'<=$(vPriorYearDate)'},MonthYear=,[Litige voor KDC]={Ja}>}Waarde) + sum({$<Year=,Month=,Day=,Calendar_Day={'<=$(vPriorYearDate)'},MonthYear=>}[Bex_Shipped_Value]*'0,02%')

Re: Can't get Prior Year (YTD) to work

amber2000 — Wed, 05 Aug 2015 12:07:21 GMT

Hello KN,

I already tried this out and this gives me the total for the 2 years toghether.

Also I get a value for MonthYear '-' (no month indication)

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

kavita25 — Wed, 05 Aug 2015 12:10:48 GMT

Hi,

What is your expected result where values are showing 0?

Re: Can't get Prior Year (YTD) to work

antoniotiman — Wed, 05 Aug 2015 12:17:13 GMT

I don't see 0 for 2015.

Can You post any screenshot ?

Re: Can't get Prior Year (YTD) to work

maximiliano_vel — Wed, 05 Aug 2015 13:44:31 GMT

I took the liberty to make some changes in your date fields, please check, the 0 values that appear in the column YTD Prior is because the values are accumulating from Jan.01/2014 to Aug.03/2014 (your max date in the data is Aug.03/2015). In the column YTD 2015 the 0 values is because your accumulating values from Jan.01/2015 to Aug.03/2014

Please see attached file

Regars

Re: Can't get Prior Year (YTD) to work

amber2000 — Wed, 05 Aug 2015 16:00:33 GMT

Hi Maximiliano,

The data is'nt correct anymore:

Jan 2014 should be -44.801, in your example it is -51.961

Also the values are only filled from jan 2014 untill aug 2014 (jan 2015 - aug 2015 has no values)

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

amber2000 — Wed, 05 Aug 2015 16:09:17 GMT

This screenshot is what the data should look like.

The result for 1 year (from aug-14 until jul-15) is 142.452

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

amber2000 — Wed, 05 Aug 2015 16:13:05 GMT

Hi Antonio,

All the values In the last column YTD 20140805 PriorYearDate are 0 for every month of 2015.

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

maximiliano_vel — Wed, 05 Aug 2015 18:48:44 GMT

Perhaps I did a mistake in the data model, however you should check your date fields, since there are in different formats i found some dates with YYYYMMDD format and others with the format DD/MM/YYYY, this should be corrected...

Regards

Re: Can't get Prior Year (YTD) to work

antoniotiman — Wed, 05 Aug 2015 19:03:17 GMT

Hi,

in last column set analysis is

......Calendar_day <= 20140731

so You can't have 2015 in this column.

You have 2015 in Column YTD Max Year

Re: Can't get Prior Year (YTD) to work

kavita25 — Thu, 06 Aug 2015 04:47:10 GMT

okay...the above screenshot should show as a result but on the click of what???

Please give full detail..

Re: Can't get Prior Year (YTD) to work

amber2000 — Thu, 06 Aug 2015 08:27:25 GMT

Good morning Kavita,

If I click on Year 2015 and month aug.

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

kavita25 — Thu, 06 Aug 2015 10:56:06 GMT

Check the attached qvw

Re: Can't get Prior Year (YTD) to work

amber2000 — Thu, 06 Aug 2015 12:32:11 GMT

Kavita,

This is exactly what I was looking for, you are amazing and I thank you from the bottom of my heart.

I can jump for joy

Kind regards,

Ambertje

Re: Can't get Prior Year (YTD) to work

kavita25 — Thu, 06 Aug 2015 12:36:38 GMT

hahaha...dats funny...