7 Replies Latest reply: Sep 28, 2017 9:56 PM by Aaron Alsweiler RSS

    Need data just from MonthEnd

    Aaron Alsweiler

      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.

        • Re: Need data just from MonthEnd
          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

            • Re: Need data just from MonthEnd
              Aaron Alsweiler

              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

                • Re: Need data just from MonthEnd
                  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;

                    • Re: Need data just from MonthEnd
                      Aaron Alsweiler

                      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

                      • Re: Need data just from MonthEnd
                        Aaron Alsweiler

                        Do I need to change FROM YOURQVD to something else?

                          • Re: Need data just from MonthEnd
                            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.

                              • Re: Need data just from MonthEnd
                                Aaron Alsweiler

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