4 Replies Latest reply: Sep 9, 2016 4:21 PM by Joey Lutes RSS

    Table of calculations

    Joey Lutes

      I'm attempting to create a table of calculations.  Specifically, these are slope calculations.

       

      Every week, a report is generated that lists table data as such:

       

      Key     ForecastDate     Forecast    ReportDate

      1           3/31/2016           3.79            3/1/2016

       

       

      I can use a slope formula to discern slope for each key given min(ForecastDate) and max(ForecastDate)

      Essentially,

      vDeltaX = max(ForecastDate) - min(ForecastDate)

      vDeltaY = sum({<ForecastDate = {"$(vMaxForecastDate)"}>}Forecast) - sum({<ForecastDate = {"$(vMinForecastDate)"}>}Forecast)

      vSlope = $(vDeltaX) / $(vDeltaY)

       

      I think that will work (assuming I can manage the syntax there).

       

      So now for the load.  I need to do that for every key and every report date.

      I'm looking for something like:

       

      Growth:

      Load

        Key,

        ReportDate,

        $(vSlope)

      Resident MyTable;

       

      Obviously a simplified version.  Can someone help me expound on that?

      Thanks a billion!

       

       

        • Re: Table of calculations
          Gysbert Wassenaar

          Something like this:

           

          Temp1:

          LOAD DISTINCT

               Key,

               ReportDate

          FROM

               ...source...;

           

          JOIN (Temp)

           

          LOAD

               Key,

               ForecastDate,

               Forecast

          FROM

               ...source...;

           

          Temp2:

          LOAD

               Key,

               ForecastDate,

               Forecast,

               ReportDate

          RESIDENT

               Temp1

          WHERE

               ForecastDate <= ReportDate;

           

          DROP TABLE Temp1;

           

          Result:

          LOAD

               Key,

               ReportDate,

               (FirstSortedValue(Forecast, -ForecastDate) - FirstSortedValue(Forecast, ForecastDate)) / (Max(ForecastDate) - Min(ForecastDate)) as Slope

          RESIDENT 

               Temp2

          GROUP BY

               Key, ReportDate;

           

          DROP TABLE Temp2;

            • Re: Table of calculations
              Joey Lutes

              Thanks gysbert,

               

              Ok, after a number of iterations, here's my current code - which does produce a 'slope' table, though with very few entries, and all of them blank in the 'slope' field.

               

              The questions I have here are:

              a)  Where ForecastDate <= ReportDate - I don't think I would ever expect the forecast date to be less than the report date - forecast implies future.  I'm not quite understanding this one.

              b)    (FirstSortedValue(ForecastUtilization, -ForecastDate) - what's the preceding dash for in front of ForecastDate?  If I leave it or remove it, doesn't seem to make an impact.

               

              I removed the (Growth)Temp2 table as it seemed redundant, and with it in, the Slope table errored ever time.  You'll notice I also added:

              PortKey as SlopeKey, to eliminate synthetic keys.

               

              Thoughts?

              Thank you!

              -----------------------------------

               

              GrowthTemp1:

              LOAD DISTINCT

                  PortKey,

                  ReportDate

              Resident ForecastData;

               

              JOIN (GrowthTemp1)

               

              LOAD

                  PortKey,

                  ForecastDate,

                  ForecastUtilization

              Resident ForecastData

               

              WHERE

                  ForecastDate <= ReportDate;

               

              // GrowthTemp2:

              // LOAD

              //      PortKey,

              //      ForecastDate,

              //      ForecastUtilization,

              //      ReportDate

              // RESIDENT

              //      GrowthTemp1

              // WHERE

              //      ForecastDate <= ReportDate;

               

              // DROP TABLE GrowthTemp1;

               

              Slope:

              LOAD

                  PortKey as SlopeKey,

                  ReportDate,

                  (FirstSortedValue(ForecastUtilization, -ForecastDate) - FirstSortedValue(ForecastUtilization, ForecastDate)) / (Max(Date(ForecastDate)) - Min(Date(ForecastDate))) as Slope

              RESIDENT

                  GrowthTemp1

              GROUP BY

                  PortKey, ReportDate;

               

              DROP TABLE GrowthTemp1;

                • Re: Table of calculations
                  Gysbert Wassenaar

                  a)  Where ForecastDate <= ReportDate - I don't think I would ever expect the forecast date to be less than the report date - forecast implies future.  I'm not quite understanding this one.

                  Hmm, yeah, that's should probably be >=. But with only one line of example data I really can't tell.

                    • Re: Table of calculations
                      Joey Lutes

                      Ok, So since my data was already all loaded, there was no need for the temp tables.

                      Here's what I have currently - which does, in fact, produce the table and intended results.

                       

                      Slope:

                      LOAD

                           PortKey as SlopeKey,

                           ReportDate,

                           (FirstSortedValue(ForecastUtilization, -Date(ForecastDate)) - FirstSortedValue(ForecastUtilization, Date(ForecastDate))) / (Max(Date(ForecastDate)) - Min(Date(ForecastDate))) as Slope

                      RESIDENT

                           ForecastData

                      GROUP BY

                           PortKey, ReportDate;

                       

                      SlopeTable.JPG

                       

                      So currently, the problem is - when I try to show these values in a straight table, or bar chart (with our without aggregation functions), the processing time generally times out and it completely overwhelms the application.  I would have assumed that it did the calcs during the load (as the values are in the table), so theoretically displaying them shouldn't be an issue I would have thought.  Apparently I was wrong.  It will not generate a straight table with these 3 dimensions (from the Slope table) in it.

                       

                      EDIT:

                      I think I found the problem.  I was able to get a table with PortKey and Slope with just one PortKey selected.  There are 38,777 values for that one PortKey /Port listed in the table, but just one per SlopeKey.  A SlopeKey should relate 1:1 to Port/PortKey for the Port will be the primary dimension.   Hmmmmmmmmmmm

                      If I remove the PortKey AS SlopeKey it creates a synthetic key.

                       

                      Thoughts?