A tricky problem that I am facing

[#1]

I need to draw a graph with a date and 2 expressions.

The table structure is like this and the sample data is like this

Year	Month	Week	Day	Sales	Date
2012-2013	1	1-1	1	1000	11-Jul-12
2012-2013	1	1-1	1	300	11-Jul-12
2012-2013	1	1-1	2	500	12-Jul-12
2012-2013	1	1-1	3	200	13-Jul-12
2013-2014	1	1-1	1	2500	16-Jul-13
2013-2014	1	1-1	1	1500	16-Jul-13
2013-2014	1	1-1	2	5000	17-Jul-13
2013-2014	1	1-1	3	2500	18-Jul-13
		My chart should look like this
		Date	This Year	Last Year
		16-Jul-13	4000	1300
		17-Jul-13	5000	500
		18-Jul-13	2500	200

Should get 4000 based on the 2 records for 16-Jul, and 1300 is derived based on the same month, week and day in the previous year 2012-2013. 11-Jul-12 is the equivalent day in the previous year and the sum 1300 hence. and so on.

I tried something like this for the second expression but it just gives me the same value as the 1st expression.

=Sum( {<[Sale_year]={"=SubField([Sale_year], '-')-1  & '-' & SubField([Sale_year], '-')"} >} [Sales])

Can you kindly advise. The crux of this is the year in the second expression points to the previous years but all the other columns should match. (Month, week and day).

Thanks,

Ram

[tresesco]

This is something you have to work in the script for. You have to create an additional field for last year data. As Of Date approach is the one you have to follow. PFA, hope, that would give you an idea.

[#3]

Thanks treseco. Isn't there any other simple way by which I can manipulate expression to point to a date in the previous year? My case is I need to use some 4 fields to go back and find the equivalent date last year.

[sujeetsingh]

Tresesco,

You just written a to the point script.

Ram just what you have to do is to just maintain months and dates to in the script and you can get what you want.

[tresesco]

I won't say that 'it is impossible' to achieve in the front-end. Probably you can, using complex logic with concat function. But that would be too cumbersome. It is always recommended to use the script way. In qlikview AS Of Date  is much known for the similar requirement and you would find post by John Witherspoon in the community. I would suggest you to go through the same; take the pain once and gain for ever.

[#6]

Thanks treseco. What you say makes perfect sense. And it worked as expected. But there is still one issue. Once I change the year format from 2013 to '2013-2014', the value under 'Previous' shows the sum of 'Current' and 'Previous'. Not sure why. Can you please look at the attached?

Thanks once again.

regards!

[jolivares]

I think that is not "possible" (not necessarily impossible in QV) because you're trying to show results over a different dimension,  If you choose the day it's better:

This Year : Sum(If(Year(Date(Date#(F6,'D-MMM-YY')))=Year(Today()),F5))

Last Year : Sum(If(Year(Date(Date#(F6,'D-MMM-YY')))=Year(Today())-1,F5))

Check the initial setting for the date variable: SET DateFormat='DD/MM/YYYY';

[tresesco · Accepted solution]

PFA. Hope this helps.

[#9]

It is the same script that Treseco provided. I value of Year is 2013-2014 instead of 2013. That is the only difference. I am hoping this to work.

@Treseco, Kindly help.

[#10]

Thanks a ton as usual Treseco.