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]

Sorry, i did not get the issue.

[#3]

I posted the message before you added the text function around Subfield. Everything working perfectly now. Thanks a ton Treseco.

[#4]

Hi Treseco, Sorry to trouble you again. I am facing one more challenge. Now I want to display the date from the current year and not the date from the last year, though I wan to display the figures from the previous year.

If you look at the righmost chart in the attached file the chart displays 03/07/2013 and 10/07/2013 as the dates from the current year.

The chart now displays 4 rows where I expect to see only 2 rows as follows. i.e. I need to display the dates only from the current year.

DateF          Year          Month     Week     Day     Current            Previous

03/07/2013  2013-2014     1          1-1          1          720               220

10/07/2013  2013-2014	2	2-2	2	410                60

Thanks,

Ram

[tresesco]

Hi,

Please create a new thread to get more attention.

[#6]

Sure. Done.