Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Not applicable

Need data just from MonthEnd

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 = BrokenSmiley Sad

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.

7 Replies
eduardo_dimperi
Valued Contributor II

Re: Need data just from MonthEnd

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

Not applicable

Re: Need data just from MonthEnd

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_dimperi
Valued Contributor II

Re: Need data just from MonthEnd

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;

Not applicable

Re: Need data just from MonthEnd

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

Not applicable

Re: Need data just from MonthEnd

Do I need to change FROM YOURQVD to something else?

eduardo_dimperi
Valued Contributor II

Re: Need data just from MonthEnd

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.

Not applicable

Re: Need data just from MonthEnd

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%';