Rolling Data In Forecast And Cumulative Totals Question
Oct 12, 2007
I have a result set that looks like this:
Code Block
Quarter
Year
EstimatedValue
ClosePercent
EstimatedCloseDate
4
2007
100000
50
12/31/07 5:00 AM
4
2007
20000
50
11/30/07 5:00 AM
4
2007
20000
90
10/30/07 5:00 AM
1
2008
278000
50
3/31/08 5:00 AM
4
2007
200000
50
11/30/07 5:00 AM
4
2007
225000
90
10/31/07 5:00 AM
4
2007
36500
90
10/31/07 5:00 AM
4
2007
80000
90
10/31/07 5:00 AM
4
2007
107200
90
10/31/07 5:00 AM
4
2007
225000
75
12/31/07 5:00 AM
4
2007
35000
50
12/31/07 5:00 AM
I have create a simple tabular rolling forecast report (with cumulative totals) from today (October) thru the next 12 months that looks like this. It smartly works no matter when the report is generated, by starting with this CurrentMonth and moving forward by using 1,2,3,4,etc. in the dateadd: =MonthName(datepart("m",dateadd("m",1,Now())))
The report sample (formatting lost in dropping it in here):
Code Block
Close Pct
October
November
December
January
February
25.%
$0
$0
$26,625
$0
$0
50.%
$237,500
$110,000
$262,500
$0
$0
75.%
$56,250
$0
$891,075
$0
$0
90.%
$1,051,830
$0
$0
$0
$0
Monthly Total
$1,345,580
$110,000
$1,180,200
$0
$0
Cumulative Total
$1,345,580
$1,455,580
$2,635,780
$2,635,780
$2,635,780
It is working fine....there doesn't seem to be anything wrong with it (all numbers total correctly, etc.), but it is very unelegant.....and I know there must be a better way.
In the righthand most month (which would be September 2008) column, I have a formula that produces the amount (the Monthly Total amount is the same):
I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
I had a need to update a column in a table with a calculated value based on the value of the preceeding value in the table (which in turn was based on the preceeding value, etc...).
Although this can easily solved via a cursor, I was able to solve this with a single set statement that first updates a variable (which can be part of a calculation using the 'current' row) and using the knowledge that SET statements are applied 'in order' (top-down).
Most importantly: the technique relies on my observation that the SET statement processes a table in clustered index sequence, or load sequence in a heap table.
I've included a very simple example of this technique below.
I'm curious if anyone is using this (undocumented?) technique and if my assumptions are valid (I'm using this on much larger tables with more complex calculations).
EXAMPLE:
CREATE TABLE #tmp ( num int IDENTITY(1,1) NOT NULL, val int NULL)
-- load 100 test rows DECLARE @x int SET @X = 1 WHILE @X <= 100 BEGIN INSERT #tmp (val) VALUES (NULL) SET @X=@X+1 END
DECLARE @i int SET @i = 1
UPDATE #tmp -- this is a -very- simple example of technique! SET @i=num+@i, -- @i now has current totaled value + current-row int value val=@i -- val now stores current subtotal
These are performing extremely poorly and worse compared to our older SQL Server 2000 environment. Is there anything obvious from this formula that would make it worse?
I appreciate any guidance as to what I should look for.
I am making a report for a car dealer. Each month a certain number of cars will be placed on the lot and a certain number will be taken off. I want to make a report that will show the inventory levels at any point in time. (I will further refine the report by color, origin, model, etc later.) I am currently listing each car in my inventory table, flagging it with a "removed" flag when it is sold. My flag for adding a car is 1 and removing a car is -1. It's easy enough to sum the table and get the current inventory level but I'm having trouble looking back over time.
Say in January I had 35 cars on the lot. I sold 20 and added 5. In February I sold 9 and added 10. In March I sold 12 and added 5.
I need the report to break every month. But on the break I need to add all the cars in the table minus cars sold. For the January report it should show a total (cars added - sold) in the table. Each month I need to take a new grand total of all cars added minus cars sold up to the end of that month OR cars added that month minus cars sold that month added to the running total from the previous month.
Is there some sort of calculate measure I could stick in my cube that would carry these totals? Or any other ideas?
I am trying to display a line chart with cumulative totals over period of 12 months in a fiscal year. I know this can be achieveable in report designer using "Running value function".
Any idea how to achieve the same in Report builder ?
I have a Problem with cumulative totals of DistinctCount of customers while working in the MDX-Query-Designer of Analysis Services. The measure [Measures].[DC Customer] is a distinct count of CustomerNr of Customers who have purchased products. I use
With Member [Measures].[DC Custumer kum] As 'Sum (Periostodate([Time].[Year]),[Measures].[CD Customer kum]' Select {[Measures].[DC Custumer],[DC Custumer kum]} on colums, [Productgroup].[Maingroup].members on rows From Sales Where [Time].[All_Time].[2005].[March]
but it returns the sum of [Measures].[DC Customer] and added the January, February and March value. I need to get the value of customers who have purchased products from january to march.
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
Hi All, I wanna pull a 12 month rolling data and donno how to do that, can anybody help ? Problem: lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype. Appreciate your help. Thanks Chinna
Hi,There are 3 tablesTable,TableDetails,TableDaily.With structureTABLE:TableID UserID Money---------- ---------- ----------(int) (int) (money)TABLEDETAILS:TableDetailsID TableID ItemID PaidForItem DayID---------- ---------- ---------- ---------- ----------(int) (int) (int) (money) (int)TABLEDAILY:TableDailyID TableID PaidForItem Money Total Change---------- ---------- ---------- ---------- ---------- ----------(int) (int) (money) (money) (PaidForItem + Money) (money)"Table" holds id for user and his money amount, which changes during time. "TableDetails" holds data about items user bought, amount paid for them and dayid which relates to one particular day."TableDaily" holds history. I do not know how to update this table.I created job whish runs stored procedure. This procedure sums "PaidForItem" using group by TableID and WHERE DAYID = '11'.Problem is with Change column. This column sould hold difference between today's Total and previous one etc.Current procedure looks like this:INSERT INTO TableDaily (TableID, PaidForItem, Money, DayID)SELECT TableDetails.TableID, SUM(PaidForItem) AS PaidForItem, Table.Money, (SELECT DayID FROM Days WHERE (Aktive = 1)) AS DayIDFROM TableDetails INNER JOIN Table ON TableDetails.TableID = Table.TableID GROUP BY TableDetails.TableID, Table.Money
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example: ... .... Group1        Value               10               20 Sub Total 1:     30
Group2         Value                15                25 Sub Total 2:      40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
INSERT INTO #t1 (CODE,COLUMN1,COLUMN2) VALUES ('432', 0,100), ('TOTAL FOR 432',0,100), ('4320001',0,250), ('4320001',50,0), ('4320001',0,140), ('4320001',300,0), ('TOTAL FOR 4320001',350,390), ('432002',200,0), ('432002',0,100), ('TOTAL FOR 432002',200,100) drop table #t1
I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.
I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.
Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.
Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this
Using the TimeSeries algorithm, how do I forecast more than one time period ahead? I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces?
I have few more clarification regarding time series. Firstly In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year. Eg: 2006-01-01, 2006-02-01€¦€¦€¦. etc. all in (yy-mm-dd) format But when I make prediction for next five months, though it makes monthly predictions the date part for the months are random whereas I expect the date part to be 1st of every month. What is the reason for this and how can I overcome it. Secondly Predicted sales values for some time period are negative though I do not have any negative value in the training data. What is the reason for this and how can I rectify it?
Thirdly In one of your earlier posts you had said that the time series algorithm does not have any built in time intelligence but uses the key time column as a time sequence stamp. So If have to make predictions for a particular time period where the time slice for each time period is 25 days or 50 days etc, then I understand that the input data used to train the model should be in the same time sequence. Or Can I specify the span of the time period according to which the prediction needs to be made? Basically how can I use the same time series model to make monthly, yearly, quarterly, daily or predictions or for custom time period like I have mentioned above.
Attached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
I have built a time series model to forecast sales value
I have data from jan 2004 to jan 2006 and the sales value is at a day level in my database. But I am aggregating it to month level in the DSV of the mining model.
I am required to make only historical predictions using the above model starting form jan 2004 to jan 2006 for every month.
I have set Historical_Model_Count and Historical_Model_Gap parameter values to 24 and 10 respectively, and trying to predict for the past few months (PredictTImeseries(SalesValue,-1,1))
But its throwing me the following error
Error(Data Mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, Sales Forecast, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process
In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values are
I am not able to figure our why this problem is happening?
What should the parameter values for the above scenario?
It would also be helpful if I can get an explanation on how these two parameters affect the historical predictions. I kind of understand that these two parameters are important for historical predictions but don€™t know why or how.
I am new to SSAS and i want to try to build a "Sales" model. I will have some "Usage" data for some timespanns, but I am not quite sure how to tackle this. Is there somewhere a "Howto" for this?
Edit: There are several locations, and for each location a forecast is needed. And the Icing would be If I would be able to tell where my supplies must go 1st to achieve the best sales...
The potential Client wants to use Oracle but I would like to show them that SQL Server is the better tool for this ;)
I have created a DTS Package that does the following: 1 Delete all data from table 1 (SQL Task) 2 Import Data from .csv file into table 1 (Data import) 3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)
This all works fine, but now i want a rollback function in step 1 and 2. So when Step 1 is finished and something goes wrong i want the deleted data back.
Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS. thoughts?
One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock: This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised: This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
We have an inventory of devices we service and wish to show the total numbers of active devices per month going 12 months back. How would I go about this?
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)
I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??
CREATE PROCEDURE Addrecords AS
--USERS INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID]) SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber] FROM production WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail) AND validAD=1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END
--PROPERTY INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId]) SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId] FROM [Production].[dbo].[Temp] WHERE AdvertiserEmail IS NOT NULL AND validAD=1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END
trying to develop a report that shows the sum total for each month during a specified date range.
I have parameters asking for the start date and end date of the date range, the report needs to show the months in between this date range.
One of the fields in the dataset is durationminutes which I need to sum for each month then divide by 60 to show hours. I then need to show the total of each month in a bar graph (with each month along the x axis).
If anyone can shed some light on how I am going to do this it would be greatly appreciated.
I'm trying to figure out how to this do with TSQL and preferablythrough joins rather than by using cursors or temp tables. I havemultiple time spans for multiple individuals that I would like torollup together where they don't actually have a gap in coverage.ID StartDate EndDateZ000001 01/01/1996 9/30/1996Z000001 10/01/1996 12/31/1998Z000001 01/01/1999 04/30/1999Z000001 01/01/2000 12/31/2000Z000001 01/01/2001 12/31/2001I would like to be able to "roll these up" in order to reflect theactual time frames so that it would like this:ID StartDate EndDateZ000001 01/01/1996 4/30/1999Z000001 01/01/2000 12/31/20014/30/1999 to 01/01/2000 is, of course, a legitimate break where therewas a date difference of more than one day.Thanks in advance for any ideas.
I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit
Is database mirroring compatible between SQL Server 2005 (Standard Edition) SP1 and SP2? I currently run SP1, and will shortly be introducing a failover partner. I'd like to have the failover partner installed as SP2, start the mirroring, manually failover, then upgrade the existing server to SP2 as well.
Is this a supported upgrade method, or will I need to upgrade the existing server to SP2 before setting up database mirroring? The SP2 installation instructions and readme file don't appear to mention mirroring at all (at least not that I can find).
