Caluculate Dim

[subbareddykm]

Hi All ,

i m trying to create one caluculate dim in my chart.

i.e In my data i have a Project and phases and respective dates.

What i wan is , if i m in Phase -B i want to show only greater then dates only.

For ref Data:

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	29/06/2016	31	42
		27/07/2016	31	42
		10/08/2016	31	42
		17/08/2016	40	42
	Phase-C	31/05/2017	40	42
		07/06/2017	40	42

[subbareddykm]

Input Data:

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	15/06/2016	5	6
		22/06/2016	12	14
		17/08/2016	31	42
		24/08/2016
		02/09/2016	40	42
	Phase-C	31/05/2017	40	42
		07/06/2017	40	42

Output Is :

Project	Sub Proj	Date	Val	Val 2
Project A	Phase-A	01/06/2016	0	0
		08/06/2016	0	0
		15/06/2016	5	6
		22/06/2016	12	14
		29/06/2016	18	31
		06/07/2016	19	31
		13/07/2016	19	31
		20/07/2016	22	31
		27/07/2016	26	31
		03/08/2016	31	39
		10/08/2016	31	42
	Phase-B	17/08/2016	31	42
		24/08/2016	31	42
		02/09/2016	40	42
	Phase-C	31/05/2017	40	42
		07/06/2017	40	42

I hope u understood.

[sunny_talwar]

How do you decide to pick everything after 17/08/2016? What is so special about that date?

[varshavig12]

What little I understood is,

Your Phase A ends at 10th aug, 2016,

so in your chart, you want phase B to start after 10th aug date i.e 17/08/2016 as per the above sample data. Right ?

[subbareddykm]

If u check my last reply , i mentioned there , For Every Sub_proj the Date should be greater than of last Sub_proj of Max date...

[subbareddykm]

Yes exactly

[tresesco · Accepted solution]

Try like:

Table:

Load

  Autonumber(Project&"Sub Proj") as Key,

  Project,"Sub Proj",

  Date(Date) as Date,

  Val,

  "Val 2"

  Inline [

Project,Sub Proj,Date,Val,Val 2

Project A,Phase-A,42522,0,0

Project A,Phase-A,42529,0,0

Project A,Phase-A,42536,5,6

Project A,Phase-A,42543,12,14

Project A,Phase-A,42550,18,31

Project A,Phase-A,42557,19,31

Project A,Phase-A,42564,19,31

Project A,Phase-A,42571,22,31

Project A,Phase-A,42578,26,31

Project A,Phase-A,42585,31,39

Project A,Phase-A,42592,31,42

Project A,Phase-B,42550,31,42

Project A,Phase-B,42578,31,42

Project A,Phase-B,42592,31,42

Project A,Phase-B,42599,40,42

Project A,Phase-B,42810,40,42

Project A,Phase-B,42920,40,42

Project A,Phase-C,42886,40,42

Project A,Phase-C,42893,40,42

];

Left Join

load

  *,

  Peek(MaxDate) as PreMaxDate;

Load

  Autonumber(Project&"Sub Proj") as Key,

  max(Date) as MaxDate

Resident Table Group By Project,"Sub Proj";

Final:

Load

  Project,

  "Sub Proj",

  Date,

  Val,

  "Val 2"

Resident Table Where len(trim(PreMaxDate))=0 or Date>PreMaxDate;

DROP Table Table;

Note: This actually loads all dates (for the subsequent Sub Proj) when it's Date is greater than earlier Sub Proj's maximum date. So with this logic data load order matters. Hence you might have to use ORDER BY clause additionally in real scenario.