7 Replies Latest reply: Jan 14, 2012 11:44 PM by Qaiyum Shaikh RSS

    how to to get the values for last working day of each month

      Hi,

       

      I am newbie to qlikview and started to create some basic stuffs in qliekiew desktop personal edition. I am in the process of getting values of last working day of each month. Here is the sample data on which i am working.

       

      datePrice
      04/01/20110.85
      31/01/20110.86
      01/02/20110.861
      28/02/20110.865
      01/03/20110.87
      29/03/20110.869

       

      I am getting this data as chart of type Straight table. One of the dimension is to get the last working date and i am able to retreive this using the following formula.

                aggr(max(date),MonthName) ..MonthName is a calculated field in the load script.

       

      Please help me out in getting the prices of the corresponding last working date. the result i am expecting is

       

      datePrice
      31/01/20110.86
      28/02/20110.865
      29/03/20110.869

       

       

      Thanks,

      Qaiyum

        • Re: how to to get the values for last working day of each month
          Sokkorn Cheav

          Hi Qaiyum,

           

          Let try this script

          SET ThousandSep=',';
          SET DecimalSep='.';
          SET MoneyThousandSep=',';
          SET MoneyDecimalSep='.';
          SET MoneyFormat='$#,##0.00;($#,##0.00)';
          SET TimeFormat='h:mm:ss TT';
          SET DateFormat='DD-MM-YYYY';
          SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';
          SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
          SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
          
          [Data]:
          LOAD * INLINE [
          date,    Price
          04-01-2011,    0.85
          31-01-2011,    0.86
          01-02-2011,    0.861
          28-02-2011,    0.865
          01-03-2011,    0.87
          29-03-2011,    0.869];
          
          [Data1]:
          LOAD
              date        AS Date1,
              month(date)    AS Months1,
              Price        AS Price2
          RESIDENT [Data];
          
          [Data2]:
          INNER JOIN ([Data1])
          LOAD
              DATE(MAX(Date1))    AS Date1,
              Months1
          RESIDENT [Data1] GROUP BY Months1;
          

          Let me know if this one help you.

           

          Regards,

          Sokkorn Cheav