Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
Can you please provide the expected output ?
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 | 40 | 42 | |
Phase-C | 31/05/2017 | 40 | 42 | |
07/06/2017 | 40 | 42 |
Hi Subbareddy,
Can you please give more details about (Dimension, Calculated Dimension, Expressions used upto what you have finished ) & Input data ( as in the source ) & Output view expected .
Thanks.
Like this may be?
In this It is ok But, What about Below case:
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 | 40 | 42 | ||
Phase-C | 31/05/2017 | 40 | 42 | |
07/06/2017 | 40 | 42 |
It will pick only one max date right.
What exact i m saying is , my dates should be gretter then of Last Sub_Proj for all.
If it is difficult get it in front end and back is also ok to me ...
Thankx in Adv
Not sure about others, but I still don't understand the logic behind choosing what dates you want to show for Phase-B. Can you elaborate or explain with an example?
Sunny,
You are right I think.
I am also thinking the same.
Thanks
From This Data, What is the expected o/p. Can you elaborate more may be