
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 = 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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Do I need to change FROM YOURQVD to something else?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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%';
