topic Re: Max date based on Quarters in QlikView

Max date based on Quarters

Thu, 06 Aug 2015 10:09:33 GMT

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;

Re: Max date based on Quarters

marcus_sommer — Thu, 06 Aug 2015 16:55:14 GMT

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

Re: Max date based on Quarters

swuehl — Thu, 06 Aug 2015 17:15:25 GMT

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.

Re: Max date based on Quarters

Fri, 07 Aug 2015 07:28:34 GMT

what if i have my Q1 starts from Nov.

Re: Max date based on Quarters

marcus_sommer — Fri, 07 Aug 2015 08:07:17 GMT

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

Re: Max date based on Quarters

swuehl — Fri, 07 Aug 2015 08:56:17 GMT

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