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

# Table of calculations

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:

Key,

ReportDate,

\$(vSlope)

Resident MyTable;

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

Thanks a billion!

• ###### Re: Table of calculations

Something like this:

Temp1:

Key,

ReportDate

FROM

...source...;

JOIN (Temp)

Key,

ForecastDate,

Forecast

FROM

...source...;

Temp2:

Key,

ForecastDate,

Forecast,

ReportDate

RESIDENT

Temp1

WHERE

ForecastDate <= ReportDate;

DROP TABLE Temp1;

Result:

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

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:

PortKey,

ReportDate

Resident ForecastData;

JOIN (GrowthTemp1)

PortKey,

ForecastDate,

ForecastUtilization

Resident ForecastData

WHERE

ForecastDate <= ReportDate;

// GrowthTemp2:

//      PortKey,

//      ForecastDate,

//      ForecastUtilization,

//      ReportDate

// RESIDENT

//      GrowthTemp1

// WHERE

//      ForecastDate <= ReportDate;

// DROP TABLE GrowthTemp1;

Slope:

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

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

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:

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;

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?