Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Group dates by month

I have a ton of dates over the last calendar year that I would like to group by month for a line chart I'm developing. Is there a way to do this?

1 Solution

Accepted Solutions
rajni_batra
Valued Contributor

Re: Group dates by month

LOAD [Chg Tx ID],

     [Patient Name],

     MRN,

     [Unique MRNs],

     [MRN + Month+FY],

     [Unique MRN/Month],

     DOB,

     [Chg Service Date],

     month([Chg Service Date]) as [Chg Service Month]

     FY,

     [MRN/FY],

     [Unique MRN/FY],

     Month,

     [Month/Type/FY],

     [Chg Post Date],

     [Current Payor Name],

     [Current Plan Name],

     Type,

     Department,

     Specialty,

     [POS Type],

     POS,

     [Guarantor Name],

     [Patient Country],

     [Patient Country Name],

     CPT,

     [CPT Desc],

     Vol,

     [Chg Amt],

     Pmts,

     [Projected Pmts],

     [Total Adj],

     [Bal Due]

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD [Hospital  Account ID],

     [Patient Name],

     [Admission Date],

     [Discharge Date],

     [Length of Stay(LOS)],

     [Patient Mrn],

     [Primary Payor Name],

     [Current Plan Name],

     [Chg Service Date],

     month([Chg Service Date]) as [Chg Service Month]

     FY,

     [Tx Post Date],

     [Procedure Code (CPT/HPCS)],

     [Procedure Desc],

     [Transaction Quantity],

     [Chg Amt],

     Trans_Base_Class,

     [Ub Rev Code ID],

     [Patient Country Name]

FROM

(ooxml, embedded labels, table is Sheet1);

It will return you month of date respective date in the same manner YEAR can be calculated

Hope it helps!!!

3 Replies
Not applicable

Re: Group dates by month

In your load script add Month(Date) or Date(Date,'MMM')

Not applicable

Re: Group dates by month

I'm not sure how to do this. I've included my load script below. The date I want to change is Chg Service Date. Can you please tell me where I add the Month(Date)?

LOAD [Chg Tx ID],

     [Patient Name],

     MRN,

     [Unique MRNs],

     [MRN + Month+FY],

     [Unique MRN/Month],

     DOB,

     [Chg Service Date],

     FY,

     [MRN/FY],

     [Unique MRN/FY],

     Month,

     [Month/Type/FY],

     [Chg Post Date],

     [Current Payor Name],

     [Current Plan Name],

     Type,

     Department,

     Specialty,

     [POS Type],

     POS,

     [Guarantor Name],

     [Patient Country],

     [Patient Country Name],

     CPT,

     [CPT Desc],

     Vol,

     [Chg Amt],

     Pmts,

     [Projected Pmts],

     [Total Adj],

     [Bal Due]

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD [Hospital  Account ID],

     [Patient Name],

     [Admission Date],

     [Discharge Date],

     [Length of Stay(LOS)],

     [Patient Mrn],

     [Primary Payor Name],

     [Current Plan Name],

     [Chg Service Date],

     FY,

     [Tx Post Date],

     [Procedure Code (CPT/HPCS)],

     [Procedure Desc],

     [Transaction Quantity],

     [Chg Amt],

     Trans_Base_Class,

     [Ub Rev Code ID],

     [Patient Country Name]

FROM

(ooxml, embedded labels, table is Sheet1);

rajni_batra
Valued Contributor

Re: Group dates by month

LOAD [Chg Tx ID],

     [Patient Name],

     MRN,

     [Unique MRNs],

     [MRN + Month+FY],

     [Unique MRN/Month],

     DOB,

     [Chg Service Date],

     month([Chg Service Date]) as [Chg Service Month]

     FY,

     [MRN/FY],

     [Unique MRN/FY],

     Month,

     [Month/Type/FY],

     [Chg Post Date],

     [Current Payor Name],

     [Current Plan Name],

     Type,

     Department,

     Specialty,

     [POS Type],

     POS,

     [Guarantor Name],

     [Patient Country],

     [Patient Country Name],

     CPT,

     [CPT Desc],

     Vol,

     [Chg Amt],

     Pmts,

     [Projected Pmts],

     [Total Adj],

     [Bal Due]

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD [Hospital  Account ID],

     [Patient Name],

     [Admission Date],

     [Discharge Date],

     [Length of Stay(LOS)],

     [Patient Mrn],

     [Primary Payor Name],

     [Current Plan Name],

     [Chg Service Date],

     month([Chg Service Date]) as [Chg Service Month]

     FY,

     [Tx Post Date],

     [Procedure Code (CPT/HPCS)],

     [Procedure Desc],

     [Transaction Quantity],

     [Chg Amt],

     Trans_Base_Class,

     [Ub Rev Code ID],

     [Patient Country Name]

FROM

(ooxml, embedded labels, table is Sheet1);

It will return you month of date respective date in the same manner YEAR can be calculated

Hope it helps!!!

Community Browser