Need data just from MonthEnd

[#1]

Hi,

I have a table that lists a number ("OCCUPUNITNO") for every day but need to do a line chart that just shows the "OCCUPUNITNO" numeric for the last day of every month.

Current Dimension =MonthEnd(ActivityDate)

Measure = Broken:(

I can get an average for the month with AVG(OCCUPUNITNO) but just cannot get the data from one day.

Any help greatly appreciated please. I am sure this is simple and I am being dumb.

[eduardo_dimperio]

Can you put in your script this:

AUX:

LOAD

OCCUPUNITNO,

Month(ActivityDate) AS MONTH,

Day(ActivityDate) AS DAY

FROM YOURQVD

FINAL:

LOAD

MAX(DAY),

MONTH,

OCCUPUNITNO

RESIDENT AUX

GROUP BY

MONTH,

OCCUPUNITNO

;

Drop Table AUX;

This will show only the last day of the month where OCCUPUNITNO occur, if i right understant  your need

[#3]

Hi,

Thanks for your response. I get the following error.

The error occurred here:

AUX: LOAD OCCUPUNITNO, Month(ActivityDate) AS MONTH, Day(ActivityDate) AS DAY FROM YOURQVD FINAL: LOAD MAX(>>>>>>DAY<<<<<<), MONTH, OCCUPUNITNO RESIDENT AUX GROUP BY MONTH, OCCUPUNITNO

[eduardo_dimperio]

Ah, sorry i think that in english DAY is a reserved word.

Try this

AUX:

LOAD

OCCUPUNITNO,

Month(ActivityDate) AS SELECT_MONTH,

Day(ActivityDate) AS SELECT_DAY

FROM YOURQVD

FINAL:

LOAD

MAX(SELECT_DAY),

SELECT_MONTH,

OCCUPUNITNO

RESIDENT AUX

GROUP BY

SELECT_MONTH,

OCCUPUNITNO

RESIDENT AUX;

Drop Table AUX;

[#5]

Sorry still luck.

The following error occurred:

Unexpected token: 'SELECT_DAY', expected one of: 'symbol', 'biff', 'dif', 'fix', 'html', 'json', 'kml', ...

The error occurred here:

AUX: LOAD OCCUPUNITNO, Month(ActivityDate) AS SELECT_MONTH, Day(ActivityDate) AS SELECT_DAY FROM YOURQVD FINAL: LOAD MAX(>>>>>>SELECT_DAY<<<<<<), SELECT_MONTH, OCCUPUNITNO RESIDENT AUX GROUP BY SELECT_MONTH, OCCUPUNITNO

[#6]

Do I need to change FROM YOURQVD to something else?

[eduardo_dimperio]

Yeah!

YOURQVD means the address where your file are, like C:\\YOURQVDNAME, this could even not be a qvd but a excel file or a select in a DB.

[#8]

OK I need to connect it to my DB. So what would I use if  current SQL looks like...

LIB CONNECT TO 'SSHOF';

SQL SELECT ActivityDate,

    ActivityWeek,

    ActivityMonth,

    SortReport,

    CalendarNotes,

    CalcErrors,

    LastUpdate,

    LastUpTime,

    DiscUnitNo,

    DiscArea,

    FacilityCode,

    AvgStay,

    InquiriesTaken,

    Conversions,

    ConvertTime,

    ResvtnsTaken,

    ResvtnsCancel,

    MoveIns,

    MoveOuts,

    TotUnitNo,

    OccupUnitNo,

    VacantUnitNo,

    OtherUnitNo,

    TotUnitArea,

    OccupArea,

    VacantArea,

    OtherArea,

    TotValue,

    OccupValue,

    OccupValActual,

    VacantValue,

    OtherValue,

    RentChg,

    RentRec,

    DepositChg,

    DepositRec,

    LateFeeChg,

    LateFeeRec,

    InsureChg,

    InsureRec,

    MerchChg,

    MerchRec,

    OtherChg,

    OtherRec,

    TaxChg,

    TaxRec,

    LateFeeWaived,

    ActiveAgreemts,

    InsureAgreemts,

    InsureNew,

    InsureCancel,

    Arrears1No,

    Arrears2No,

    Arrears3No,

    Arrears4No,

    AdvanceBillNo,

    AdvancePaidNo,

    Arrears1Value,

    Arrears2Value,

    Arrears3Value,

    Arrears4Value,

    AdvBillRntValue,

    AdvancePaidValue,

    DepositsHeld,

    CashRec,

    CreditRec,

    Refunds,

    DiscChg,

    DiscRec,

    MktgEmailSent,

    MktgLetterSent,

    InsureUnits,

    InsureValue,

    ArrearsInactiveNo,

    ArrearsInactiveValue,

    AdvanceBillOtherValue,

    Checksum,

    MerchReturnChg,

    MerchReturnRec,

    BadDebtChg,

    BadDebtRec,

    AdvBillInsValue,

    AdvBillLateValue

FROM Activity WHERE ActivityDate > '31/12/14' AND ActivityDate < '$(vToday)' AND FacilityCode LIKE 'SSP%' OR FacilityCode LIKE 'SSWSG%';