Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Max date based on Quarters

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;

Tags (2)
5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Max date based on Quarters

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

Highlighted
MVP
MVP

Re: Max date based on Quarters

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.

Highlighted
Not applicable

Re: Max date based on Quarters

what if i have my Q1 starts from Nov.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Max date based on Quarters

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

Highlighted
MVP
MVP

Re: Max date based on Quarters

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