Date calculations - Month this year & month last year

[ljdlumley]

Dear Qlikview Community,

I'm trying to pull together a comparison report on Projects from last year to this year. Just to make it slightly more complicated I'm working in Academic Years starting 1st August and have been trying to use YearToDate(`Date Submitted`,0,8) as YTDFlag etc. Individually they work out fine and I can look at months, quarters and years. What I'm struggling with is pulling them into one table.

My data looks like the table below, all the dates are (DD/MM/YYYY)

Project	Value	Date Submitted	Date Notified
Project A	£500	01/08/2013	02/08/2013
Project B	£1,000	01/08/2013	01/09/2013
Project C	£100	01/10/2013	01/11/2013
Project X	£200	01/08/2014	02/08/2014
Project Y	£300	01/09/2014	02/10/2014
Project Z	£400	01/11/2014	02/11/2014

What I want to end up with is a pivot table looking like this

	Submitted		Notified
	2013/14	2014/15	2013/14	2014/15
Aug	£1,500	£200	£500	£200
Sep		£300	£1,000
Oct	£100			£300
Nov		£400	£100	£400

Projects A-C are in academic year 13/14 whilst X-Z are in 14/15. I don't know what dimension to use for my academic year because there are two, YearSubmitted and YearNotified. Similar problem with the Month Dimension as there is MonthSubmitted and MonthNotified.

Project A & B were both submitted in Aug 13 so the sum is £1500 but in the Notified column I need the value of Project A in August 13 and Project B in September 13.

As always any help on how to resolve this would be gratefully received.

thanks

John

[#2 · Accepted solution]

Change the inline load to be directly from your source and this should work:

Table:

load *  inline
[
Project, Value, Date Submitted, Date Notified
Project A, £500, 01/08/2013, 02/08/2013
Project B, £1000, 01/08/2013, 01/09/2013
Project C, £100, 01/10/2013, 01/11/2013
Project X, £200, 01/08/2014, 02/08/2014
Project Y, £300, 01/09/2014, 02/10/2014
Project Z, £400, 01/11/2014, 02/11/2014
];

Endtable:
load
Project,
Value,
'Submitted' as Type,
[Date Submitted],
Month([Date Submitted]) as Month,
Yearname([Date Submitted],0,8) as FiscalYear
resident Table;

concatenate(Endtable)
load
Project,
Value,
'Notified' as Type,
[Date Notified],
Month([Date Notified]) as Month,
Yearname([Date Notified],0,8) as FiscalYear
resident Table;

drop Table Table;

[#3]

Hope attached sample application help you..

[ljdlumley]

Thanks Carly, that works perfectly

[ljdlumley]

Thanks Bhawna, that also works perfectly