Max date based on Quarters

[#1]

Hi All,

I have a transposed script i need to find max date based on Quarters for each project.

Any suggestions?

This script helps to find Max date .

Trans3:

NoConcatenate

Load

ProjectID_SYS,

[Updated On],

[Value Before Update] as N_Customer_Traction_Status

Resident [Metrics History]

where [Metric Name]='Customer Traction Status';

Join

load

ProjectID_SYS,

Timestamp(Date(Max([Updated On]),'MM/DD/YYYY hh:mm:ss TT')) as Maxmetric

Resident [Metrics History]

where [Metric Name]='Customer Traction Status'

Group by ProjectID_SYS,[Metric Name];

Trans4:

NoConcatenate

LOAD ProjectID_SYS,[Updated On],N_Customer_Traction_Status,Maxmetric

Resident Trans3

where Maxmetric=[Updated On];

drop Table Trans3;

[marcus_sommer]

If you want the max-date per quarter you need to quarter to the max-aggregation-load:

...

Join

load

ProjectID_SYS,

Timestamp(Date(Max([Updated On]),'MM/DD/YYYY hh:mm:ss TT')) as Maxmetric,

'Q' & ceil(month([Updated On])/3) & ' ' & year([Updated On]) as YearQuarter

Resident [Metrics History]

where [Metric Name]='Customer Traction Status'

Group by ProjectID_SYS,[Metric Name], 'Q' & ceil(month([Updated On])/3) & ' ' & year([Updated On]);

...

- Marcus

[swuehl]

Or like

Trans3:

NoConcatenate

Load

ProjectID_SYS,

[Updated On],

[Value Before Update] as N_Customer_Traction_Status

Resident [Metrics History]

where [Metric Name]='Customer Traction Status';

Join

load

ProjectID_SYS,

Max([Updated On]) as [Updated On],

Quartername([Updated On]) as QuarterName

Resident [Metrics History]

where [Metric Name]='Customer Traction Status'

Group by ProjectID_SYS, Quarterstart([Updated On]);

No need for another resident load, I think.

[#4]

what if i have my Q1 starts from Nov.

[marcus_sommer]

I think the best is you mapped your financial quarter from your master-calendar to this join-table then to rebuilt it here again makes no sense.

- Marcus

[swuehl]

I believe Quarterstart() and Quartername() functions have an optional argument to change beginning of the quarter. Check the HELP for more details.