Previous() function not working with scope parameter.

This works:
=Previous(Fields!Jobs.Value)
The following variants get this error:
"The value expression for the textbox 'textbox17' has an incorrect number of
parameters for the function 'Previous'."
=Previous(Fields!Jobs.Value,"scope")
=Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
The definition found here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
m/rcr_creating_expressions_v1_61f7.asp?frame=true
Previous(Expression, AggFunction, PreviousScope, AggScope)
Ideas'In this particular case, the documentation is ahead of its time. The MSDN
documentation describes the full implementation of the previous aggregate
function in a future release.
Currently, the previous aggregate only support the first argument.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rick Todd" <rtodd@.spicer.com> wrote in message
news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> This works:
> =Previous(Fields!Jobs.Value)
> The following variants get this error:
> "The value expression for the textbox 'textbox17' has an incorrect number
of
> parameters for the function 'Previous'."
> =Previous(Fields!Jobs.Value,"scope")
> =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> The definition found here
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> m/rcr_creating_expressions_v1_61f7.asp?frame=true
> Previous(Expression, AggFunction, PreviousScope, AggScope)
> Ideas'
>|||For my situation I did a little workaround by testing the previous rows'
(using previous()) group and comparing the current rows' group and taking
action on the original field I was testing with previous() with a few IIF()
statements. Did the trick...
Thanks
"Rick" wrote:
> Thanks for the quick reply. Are there possibilities of this getting fixed
> with some future service pack?
> "Robert Bruckner [MSFT]" wrote:
> > In this particular case, the documentation is ahead of its time. The MSDN
> > documentation describes the full implementation of the previous aggregate
> > function in a future release.
> > Currently, the previous aggregate only support the first argument.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Rick Todd" <rtodd@.spicer.com> wrote in message
> > news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> > > This works:
> > > =Previous(Fields!Jobs.Value)
> > >
> > > The following variants get this error:
> > >
> > > "The value expression for the textbox 'textbox17' has an incorrect number
> > of
> > > parameters for the function 'Previous'."
> > >
> > > =Previous(Fields!Jobs.Value,"scope")
> > >
> > > =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> > >
> > > The definition found here
> > >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> > > m/rcr_creating_expressions_v1_61f7.asp?frame=true
> > >
> > > Previous(Expression, AggFunction, PreviousScope, AggScope)
> > >
> > > Ideas'
> > >
> > >
> >
> >
> >

Previous YTD

Hi all

I am trying to perform Pervious Year To Date Total (which is like YTD but for the previous year. ie. ytd would work for this year's jan -> whatever month. But I need a total from Last year from jan -> whatever year to do comparision), However I've ran into the following issues.

Since Year is a variable and it can change depending on which year you select. I tried to Return the first member of the month which in theory should be January of a Year. However when I performed the below query I got Dec which was the overall period through out the entire Month this include 2002 dec -> 2005 May period so the head returned Dec.

SELECT { head(Descendants(
[Period].CurrentMember,
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )
So then I tried to return the Head of the Set of the current year using the below query. But no matter what I do I can not get it working.

SELECT { head(Descendants(
{[Period].CurrentMember, [Period].[Month]},
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )

How would you guys usually do a previous year to date comparision ? since year is a variable

Thanks
Tom

This isn't clear. Can you be more explicit?|||hi vector

I think you've answered my question on the other thread. I will test this when I get into office first thing in the morning.

thanks alot !!

previous week date parameters

Hope someone can help.
What i am trying to achieve is to bring in results from the previous Monday
to Friday into my report through the default paramters.
This would mean that any day this week that that i run the report, it would
bring in results for Monday to Friday last week.
Does anybody know of a way to achieve this using the default parameters.
ThanksThanks for your reply.
The code works well. It brings in the correct dates i.e. Monday to Friday
last week but the time comes in as the current time i.e. whenever the report
was run. Is there any way for it to ignore the time.
Thanks again
"Ameet" wrote:
> Try This
> Put this code in the Code Tab of Report Properties...
> ---
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> ----
> Now
> Create Two Parameters named 'StartDate' and 'EndDate'
> Change Datatype to DateTime
> Select 'Non-Queried' in Available Values Option and leave it blank
> Go to Default Values and select Non-Queried...
> for 'StartDate' Parameter put this value
> ---
> =Code.GetDate(-5)
> ---
> and for 'EndDate' Parameter use this
> ---
> =Code.GetDate(-1)
> ---
> I guess, this will solve your problem. Play around with the code and see if
> its possible using one parameter.. I am bit busy today...
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > What i am trying to achieve is to bring in results from the previous Monday
> > to Friday into my report through the default paramters.
> >
> > This would mean that any day this week that that i run the report, it would
> > bring in results for Monday to Friday last week.
> >
> > Does anybody know of a way to achieve this using the default parameters.
> >
> > Thanks|||I've not got any code example, but I've got an idea that might work.
Use code to find out what day of the week today is. Then subtract the
appropriate number of days from today to get last Monday. You would have
only 7 possibilities. For example, if today was Tuesday you would subtract
8, if today is Wednesday you would subtract 9. That would be your begin
date for the report, then add 5 to get your end date.
Hope this helps. Maybe?
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:E07BC00D-D05C-4E86-9462-252AEB95A3B1@.microsoft.com...
> I still haven't found a solution to this so if anyone could help out that
> would be great.
> Thanks Paul
>
> "Ameet" wrote:
> > Well I dont think you can go with only date having parameter data type
to
> > datetime. I am not sure though.. I would appreciate if someone could
help me
> > here..
> >
> > workaround is if you could use string datatype... change parameter
> > datatype to string .. and then add this line to the code function
> > ---
> > GetDate=FormatDateTime(GetDate,vbShortDate)
> > ---
> >
> > Actually it gave me error with parameter datatype to datetime.
> >
> > "REPORT PARAMETER DOES NOT HAVE THE EXPECTED VALUE"
> >
> > soo...this is what i could find out..
> >
> > anyhelp on this .. more than appreciated..
> >
> > "pcalv" wrote:
> >
> > > Thanks for your reply.
> > >
> > > The code works well. It brings in the correct dates i.e. Monday to
Friday
> > > last week but the time comes in as the current time i.e. whenever the
report
> > > was run. Is there any way for it to ignore the time.
> > >
> > > Thanks again
> > >
> > >
> > > "Ameet" wrote:
> > >
> > > > Try This
> > > >
> > > > Put this code in the Code Tab of Report Properties...
> > > > ---
> > > > Public Shared Function GetDate(Byval value as Double)
> > > > value=(value)- Weekday(Now)
> > > > GetDate=DateAdd("d", value, Now)
> > > > End Function
> > >
> ----
> > > >
> > > > Now
> > > >
> > > > Create Two Parameters named 'StartDate' and 'EndDate'
> > > >
> > > > Change Datatype to DateTime
> > > >
> > > > Select 'Non-Queried' in Available Values Option and leave it blank
> > > >
> > > > Go to Default Values and select Non-Queried...
> > > >
> > > > for 'StartDate' Parameter put this value
> > > > ---
> > > > =Code.GetDate(-5)
> > > > ---
> > > >
> > > > and for 'EndDate' Parameter use this
> > > > ---
> > > > =Code.GetDate(-1)
> > > > ---
> > > >
> > > > I guess, this will solve your problem. Play around with the code and
see if
> > > > its possible using one parameter.. I am bit busy today...
> > > >
> > > >
> > > > "pcalv" wrote:
> > > >
> > > > > Hope someone can help.
> > > > >
> > > > > What i am trying to achieve is to bring in results from the
previous Monday
> > > > > to Friday into my report through the default paramters.
> > > > >
> > > > > This would mean that any day this week that that i run the report,
it would
> > > > > bring in results for Monday to Friday last week.
> > > > >
> > > > > Does anybody know of a way to achieve this using the default
parameters.
> > > > >
> > > > > Thanks|||You can get the previous week's date using this expression:
=DateAdd(DateInterval.WeekOfYear, -1, Fields!Date.Value). From there, you
can derive the first and last day of the week. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/findingbeginningorendofweek.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:E07BC00D-D05C-4E86-9462-252AEB95A3B1@.microsoft.com...
> I still haven't found a solution to this so if anyone could help out that
> would be great.
> Thanks Paul
>
> "Ameet" wrote:
> > Well I dont think you can go with only date having parameter data type
to
> > datetime. I am not sure though.. I would appreciate if someone could
help me
> > here..
> >
> > workaround is if you could use string datatype... change parameter
> > datatype to string .. and then add this line to the code function
> > ---
> > GetDate=FormatDateTime(GetDate,vbShortDate)
> > ---
> >
> > Actually it gave me error with parameter datatype to datetime.
> >
> > "REPORT PARAMETER DOES NOT HAVE THE EXPECTED VALUE"
> >
> > soo...this is what i could find out..
> >
> > anyhelp on this .. more than appreciated..
> >
> > "pcalv" wrote:
> >
> > > Thanks for your reply.
> > >
> > > The code works well. It brings in the correct dates i.e. Monday to
Friday
> > > last week but the time comes in as the current time i.e. whenever the
report
> > > was run. Is there any way for it to ignore the time.
> > >
> > > Thanks again
> > >
> > >
> > > "Ameet" wrote:
> > >
> > > > Try This
> > > >
> > > > Put this code in the Code Tab of Report Properties...
> > > > ---
> > > > Public Shared Function GetDate(Byval value as Double)
> > > > value=(value)- Weekday(Now)
> > > > GetDate=DateAdd("d", value, Now)
> > > > End Function
> > >
> ----
> > > >
> > > > Now
> > > >
> > > > Create Two Parameters named 'StartDate' and 'EndDate'
> > > >
> > > > Change Datatype to DateTime
> > > >
> > > > Select 'Non-Queried' in Available Values Option and leave it blank
> > > >
> > > > Go to Default Values and select Non-Queried...
> > > >
> > > > for 'StartDate' Parameter put this value
> > > > ---
> > > > =Code.GetDate(-5)
> > > > ---
> > > >
> > > > and for 'EndDate' Parameter use this
> > > > ---
> > > > =Code.GetDate(-1)
> > > > ---
> > > >
> > > > I guess, this will solve your problem. Play around with the code and
see if
> > > > its possible using one parameter.. I am bit busy today...
> > > >
> > > >
> > > > "pcalv" wrote:
> > > >
> > > > > Hope someone can help.
> > > > >
> > > > > What i am trying to achieve is to bring in results from the
previous Monday
> > > > > to Friday into my report through the default paramters.
> > > > >
> > > > > This would mean that any day this week that that i run the report,
it would
> > > > > bring in results for Monday to Friday last week.
> > > > >
> > > > > Does anybody know of a way to achieve this using the default
parameters.
> > > > >
> > > > > Thanks

previous week date parameters

Hi,
Hope someone can help. I am trying to get the default value date parameters
for the previous working week. I then want to use these values while running
the report during the following week until the next Monday. I have almost got
it working with the following:
I put the following code in 'Report Properties>Code':
Public Shared Function GetDate(Byval value as Double)
value=(value)- Weekday(Now)
GetDate=DateAdd("d", value, Now)
End Function
and then the following parameters:
Date from: =Code.GetDate(-5)
Date to: =Code.GetDate(-1)
The only issue i have with this is that it brings in the following:
Date from: 08/11/2004 14:40:54
Date to: 12/11/2004 14:40:54
I don't want it to include the time though. Just the following:
Date from: 08/11/2004 00:00:00
Date to: 12/11/2004 00:00:00
Does anybody know how to get around this?
Any help would be appreciated.
PaulTo convert the date I use .ToShortDateString()
Example:
=Today.AddDays(-1).ToShortDateString()
"pcalv" wrote:
> Hi,
> Hope someone can help. I am trying to get the default value date parameters
> for the previous working week. I then want to use these values while running
> the report during the following week until the next Monday. I have almost got
> it working with the following:
> I put the following code in 'Report Properties>Code':
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> and then the following parameters:
> Date from: =Code.GetDate(-5)
> Date to: =Code.GetDate(-1)
> The only issue i have with this is that it brings in the following:
> Date from: 08/11/2004 14:40:54
> Date to: 12/11/2004 14:40:54
> I don't want it to include the time though. Just the following:
> Date from: 08/11/2004 00:00:00
> Date to: 12/11/2004 00:00:00
> Does anybody know how to get around this?
> Any help would be appreciated.
> Paul
>|||Thanks for the reply. Unfortunately this does not work with the custom code i
am using.
Any other help would be appreciated.
Paul
"datobin1" wrote:
> To convert the date I use .ToShortDateString()
> Example:
> =Today.AddDays(-1).ToShortDateString()
>
> "pcalv" wrote:
> > Hi,
> >
> > Hope someone can help. I am trying to get the default value date parameters
> > for the previous working week. I then want to use these values while running
> > the report during the following week until the next Monday. I have almost got
> > it working with the following:
> >
> > I put the following code in 'Report Properties>Code':
> >
> > Public Shared Function GetDate(Byval value as Double)
> > value=(value)- Weekday(Now)
> > GetDate=DateAdd("d", value, Now)
> > End Function
> >
> > and then the following parameters:
> >
> > Date from: =Code.GetDate(-5)
> > Date to: =Code.GetDate(-1)
> >
> > The only issue i have with this is that it brings in the following:
> >
> > Date from: 08/11/2004 14:40:54
> > Date to: 12/11/2004 14:40:54
> >
> > I don't want it to include the time though. Just the following:
> >
> > Date from: 08/11/2004 00:00:00
> > Date to: 12/11/2004 00:00:00
> >
> > Does anybody know how to get around this?
> >
> > Any help would be appreciated.
> >
> > Paul
> >
> >

Previous version detected

I just dowmnloaded sql 2005. I have been trying to install sql 2005 install keeps saying a previous version exists cannot proceed three is no sql anywhere to be found. what do i do? The support to this point is non-existent typical MSFT if you are not the peron to help who is?

Can you go to Add/Remove Programs and let us know if you have any entries for SQL Server 2005 (including Native Client)? Do you have any other SQL Server entries in ARP?

Also, what version are you trying to install?

Thanks,

Sam Lester (MSFT)

previous value in trigger

Hi,
Can I get previous and current values of row in trigger for update
operation.
Regards,
Shah Adarsh.The previous value is in the deleted table and the current value in the
inserted table.
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140679727.735390.38640@.i39g2000cwa.googlegroups.com...
> Hi,
> Can I get previous and current values of row in trigger for update
> operation.
>
> Regards,
> Shah Adarsh.
>|||The data is stored in the virtual INSERTED table:
Operation --> Virtual Tables
INSERT --> INSERTED
DELETE --> DELETED
UPDATE --> INSERTED,DELETED
HTH, jens Suessmeyer.|||Thanks Quinn. It solved my problem.|||Thanks Jens. It solved my problem.

Previous Value Calculation driving me nuts

Hey all,
I've posted my question in the OLAP sql group, but so far no one has
responded, so im hoping you guys can help me out...
My problem is that i am trying to create a value added calculation in a
named query. The calculation i am trying looks like this in algebra
form: (current cost-previous cost)-(current profits-previous profits)
Now the previous cost or previous profits data is based on dates found
in another table (called:tbo.Dates) if that makes any difference
Costs & Profits in table called tbo.Values
If more info is needed, please let me know.
Thanks & Cheers,
Can you provide the DDL (Create Table.. etc) and some sample Data and
what the expected results are?
Barry
daveoram24@.hotmail.com wrote:
> Hey all,
> I've posted my question in the OLAP sql group, but so far no one has
> responded, so im hoping you guys can help me out...
> My problem is that i am trying to create a value added calculation in a
> named query. The calculation i am trying looks like this in algebra
> form: (current cost-previous cost)-(current profits-previous profits)
> Now the previous cost or previous profits data is based on dates found
> in another table (called:tbo.Dates) if that makes any difference
> Costs & Profits in table called tbo.Values
> If more info is needed, please let me know.
> Thanks & Cheers,

Previous Value Calculation driving me nuts

Hey all,
I've posted my question in the OLAP sql group, but so far no one has
responded, so im hoping you guys can help me out...
My problem is that i am trying to create a value added calculation in a
named query. The calculation i am trying looks like this in algebra
form: (current cost-previous cost)-(current profits-previous profits)
Now the previous cost or previous profits data is based on dates found
in another table (called:tbo.Dates) if that makes any difference
Costs & Profits in table called tbo.Values
If more info is needed, please let me know.
Thanks & Cheers,Can you provide the DDL (Create Table.. etc) and some sample Data and
what the expected results are?
Barry
daveoram24@.hotmail.com wrote:
> Hey all,
> I've posted my question in the OLAP sql group, but so far no one has
> responded, so im hoping you guys can help me out...
> My problem is that i am trying to create a value added calculation in a
> named query. The calculation i am trying looks like this in algebra
> form: (current cost-previous cost)-(current profits-previous profits)
> Now the previous cost or previous profits data is based on dates found
> in another table (called:tbo.Dates) if that makes any difference
> Costs & Profits in table called tbo.Values
> If more info is needed, please let me know.
> Thanks & Cheers,

Previous value best method

I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:

SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'

ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.Unfortunately, this is just a clunky thing to do in SQL. You can try this and see if it is any faster. One lest nested subquery...

select Current.Trolley_num,
Current.Date,
Current.Speedo_start,
Current.Speedo_end,
Previous.Speedo_end
from Daily_Trolley Current
inner join --PriorReadings
(select DTA.Trolley_num,
DTA.Date,
Max(DTB.Date) as PreviousDate
from Daily_Trolley DTA
left outer join Daily_Trolley DTB
on DTA.Trolley_num = DTB.Trolley_num
and DTA.Date > DTB.Date
group by DTA.Trolley_num,
DTA.Date) PriorReadings
on Current.Trolley_num = PriorReadings.Trolley_num
and Current.Date = PriorReadings.Date
left outer join Daily_Trolley Previous
on PriorReadings.Trolley_num = Previous.Trolley_num
and PriorReadings.PreviousDate = Previous.Date|||Forgive me if this is a duplicate; I got an error posting a reply and it's not showing up. This is the third try.

Thanks blindman. QA didn't like "Current" as an alias, but it worked fine when I changed that. Both our versions return records so quickly that no time is registered in the execution time window in QA. There are only 10k records in this table though. However, your version lets me calculate the difference; mine wouldn't (not directly anyway).

I may modify both to work against another table with several hundred thousand records, and see how they compare. I suspect yours will be faster due to the join instead of subquery.

Thanks again.

Previous row calculations using SQL

Hi,
I need help in writing a SQL, where in a calculated column depends on previo
us's row column, for example,
Table: test
Time Pkts Seq_no
--
10:00 20 25
10:01 15 40
10:02 17 57
10:03 10 60
10:04 12 72
Query Result: The output of the query should be
Time Pkts Seq_no drops
---
10:00 20 25 NULL
10:01 15 40 0
10:02 17 57 0
10:03 10 60 7 (=57+10-60)
10:04 12 72 0
Is this kind of calculation possible using SQL. Any help in this regards wil
l be highly appreciated.
Thanks
-MIf is indeed possible to do this in SQL, but you cannot do this with a
calculated column. The rules of calculated columns are that the calculation
is restricted to the current row only.
To perform this calculated you will need to create a column to store the
data. Then use an "instead of" trigger to perform the calculation, and save
the result into the correct row.
There are a couple of warnings with this method.
1. Perform as few inserts and updates as possible. Inserting 1000 rows, one
at a time will be extremely expensive. It is better to perform a single
insert of 1000 rows instead - use temporary tables or another buffering
technique to batch process.
2. You need to make sure that all the nuances of the code are taken into
account. It is vital that the correct calulation is always performed. This
is not trivial, but is possible. I know because I've done it before - but
unfortunatly cannot share the code as it's not my property.
Regards
Colin Dawson
www.cjdawson.com
"mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
news:mmonis.28cv5s@.mail.codecomments.com...
> Hi,
> I need help in writing a SQL, where in a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Pkts Seq_no
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Pkts Seq_no drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> -M
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Get a copy of SQL-2005 and look at SUM() OVER() function that is new to
Standard SQL. It can get complicated but it is used for running
totals, etc.|||Try this, note that this relies on Time being unique.
If Time isn't unique, you'll need some additional
rules to determine the "previous" row.
SELECT t1.Time,
t1.Pkts,
t1.Seq_no,
t2.Seq_no + t1.Pkts - t1.Seq_no AS drops
FROM test t1
LEFT OUTER JOIN test t2 ON t2.Time<t1.Time
AND t2.Time=(SELECT MAX(t3.Time)
FROM @.test t3
WHERE t3.Time<t1.Time)
ORDER BY t1.Time|||Have you considered doing this at select time instead of storing the value?
You certainly need to take performance into consideration, but it could be
done this way. Note that like the solution posted by MarkC600, this assumes
that time is unique.
Create view Test_vw as
Select a.time
, a.pkts
, (
select sum(b.pkts)
from test b
where b.time = a.time
) as seq_no
from test a
OR (see disclaimer below)
Create view Test_vw as
Select a.time
, a.pkts
, sum(a.pkts) over(order by a.time)
from test a
group by a.time
, a.pkts
Note that the second query syntax is likely wrong, since I don't have a
system to test it on, and put it together based on what I could quickly
gather from the web. I think you can use the functionality in SQL Server
2005, but not on 2000. You can see how it is simpler than the first query,
and the benefit (from a code maintenance perspective) you can get from
learning it if you are on 2005.
"mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
news:mmonis.28cv5s@.mail.codecomments.com...
> Hi,
> I need help in writing a SQL, where in a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Pkts Seq_no
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Pkts Seq_no drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> -M
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Sorry... I missed the entire second half of your post somehow... I thought
you were asking how to get Seq_no.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23pHFGXBgGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Have you considered doing this at select time instead of storing the
value?
> You certainly need to take performance into consideration, but it could be
> done this way. Note that like the solution posted by MarkC600, this
assumes
> that time is unique.
> Create view Test_vw as
> Select a.time
> , a.pkts
> , (
> select sum(b.pkts)
> from test b
> where b.time = a.time
> ) as seq_no
> from test a
> OR (see disclaimer below)
> Create view Test_vw as
> Select a.time
> , a.pkts
> , sum(a.pkts) over(order by a.time)
> from test a
> group by a.time
> , a.pkts
> Note that the second query syntax is likely wrong, since I don't have a
> system to test it on, and put it together based on what I could quickly
> gather from the web. I think you can use the functionality in SQL Server
> 2005, but not on 2000. You can see how it is simpler than the first
query,
> and the benefit (from a code maintenance perspective) you can get from
> learning it if you are on 2005.
> "mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
> news:mmonis.28cv5s@.mail.codecomments.com...
>

Previous Row Calculations (sql server 2000)

if anyone know a way that you can look up a value from the previous row in a View to do a calculation on (in sql server 2000)
for example:
expr1=PreviousRow.Expr1/30 + expr2 - expr3 =100

so the next row is

expr1=100/30 + expr2 - expr3 =300

so the third row is

expr1=300/30 + expr2 - expr3 =100
or tell me if it not possible please

and special thanks to Umachandar Jayachandran - MS for help.

Hi,

Im not quite sure if this is possible in a view. But you can do this in a stored proc. You can use a cursor and manually traverse your records...

cheers,

Paul June A. Domag

|||Thank you my frind for your help but I need give me some examples of the crusors to understand that...

if you can.....
|||

Hi,

Here's a sample in transact sql:

-- Declare the variables to store the values returned by FETCH.
DECLARE @.au_lname varchar(40), @.au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- here is where you would do your calculations

-- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @.au_fname + ' ' + @.au_lname

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

BTW, this would be placed inside a stored proc...

cheers,

Paul June A. Domag

|||

Bear with me here, as the info is worth it!!!

do you really need a table for this?

Ahhh, memories of my analysis classes!!!

This is what is called a Discrete Dynamic Equation (also called a Difference equation as oppposed to a differential equation) ; specifically, a Discrete first-order Affine Dynamical System

The form is:

A_n = R A_n-1 + B

The general solution for which is:
A_k=CR^k + B/(1-R)

The particular solution for n=0 is

A₀=CR⁰+ B/(1-R)=C+B/(1-R)

Solve for C

C=A₀-B(1-R)

Therefore, the particular solution for the dynamical system is:

A_k=R^k(A₀-B/(1-R))+B/(1-R)
in sql:

Create function AffineDynamic(@.A0 real, @.R decimal(16,16), @.B real, @.k int) returns decimal(32,16)
as
begin
declare @.result decimal(32,16)
if @.k = 0
set @.result = @.A0
else
set @.result Power(@.R,@.k) * (@.A0 - (@.B * Power(1-@.R, -1))) + (@.B * Power(1-@.R, -1))
return @.result
end
usage:


-- a random expr1 for row 1
DECLARE @.initial real
set @.initial = 25

-- the rate: 1/30
DECLARE @.rate decimal(16,16)
set @.rate = 0.03333333

-- a random expr2
DECLARE @.expr2 real
set @.expr2 = 23

-- a random expr3
DECLARE @.expr3 real
set @.expr3 = 7

-- First row
select 0 Iter, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,0) Expr1
union
-- Second row
select 1, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,1)
union
-- Third row
select 2, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,2)
union
-- Fourth row
select 3, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,3)
union
-- Fifth row
select 4, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,4)
union
-- Sixth row
select 5, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,5)
union
-- Sixth row
select 6, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,6)


Yields the following:

Iter Expr1
0 25.0000000000000000
1 16.8333320617675780
2 16.5611095428466800
3 16.5520362854003910
4 16.5517330169677730
5 16.5517234802246090
6 16.5517234802246090

Now, note the affinity towards 16.5517234802246090. This is called a point of stability. Any iteration after 5 for A0=25, R=1/30, B= (23-7) = 16 yeilds this number.

To wit:

select 1000 Iter, dbo.AffineDynamic(25, 0.03333333, 16,1000) Expr1

Yields (as expected):

Iter Expr1
1000 16.5517234802246090

Reference:
Discrete Dynamical Modeling, Sandefur, Oxford University Press, 1993, ISBN 0-19-508438-1

|||

Another plain and practical alternative to cursors, and/or esoteric alternates that concentrate on remembering the previous values by storing them on a temporary table that is later listed as show in sample code in the "PreviousRowCalculation" SP.

create table nums(a int, b int)
insert into nums values (0,1)
insert into nums values (1,2)
insert into nums values (2,3)
insert into nums values (3,4)
insert into nums values (4,5)
go
-- drop procedure PreviousRowCalculation
create procedure PreviousRowCalculation
as
begin
set nocount on
create table #tmp (recno int identity, a int, b int, pv int)
insert into #tmp (a,b,pv)
select a,b,0 from nums order by a
declare @.t int,@.c int
set @.t = (select count(*) from #tmp)
set @.c = 1
while @.c <= @.t
begin
update #tmp
set pv = isnull((select pv from #tmp x where x.recno=@.c-1),0) -- expr1
+ (11 + b) - (10 + a) -- + expr2 - expr3
where recno=@.c
set @.c = @.c + 1
end
select * from #tmp
drop table #tmp
end
go

PreviousRowCalculation
go

|||I want to reiterate that this only works with dynamic equations of the form:
A_n = R A_n-1 + B

This is called a First-Order Homogeneous Dynamical Equation

Which is what your request is:

========================
B = expr2 - expr3
expr1_n= [(1/30) * expr1_n-1] + B
========================

This assumes that expr2 - expr3 are constant expressions and not variable values, such as columns in the table.

There are different solutions for other forms - First-Order Non-Homogenous Dynamical Equations, N-Order Homogeneous/Non-Homogenous Dynamical Equations and Systems of Dynamical Equations, just to name a few.

Google:
Logistic Equation
Markov Chains

The point I am trying to make, if there is a distinct causal relationship between values of sequential iterations of a formula, that is:

F(x)_n = G(F(x)_n-1)

There is often an dual formula H(x , k) such that

F_k(x) = H(F(x)₀ , k)

where k is the iteration you want to calculate and F(x)₀ is the intial value, thereby eliminating the need to iterate all the values.

The trick is being able to recognize the form of the system (is it solvable?) and then applying the proper dual.

Ah, the difference between a degree and a certificate!|||

or, using ed's nums table and exprnext = exprPrev +(11 +b) - (10+a)

select cast(null as int) pv, identity(int) id, * into #temp from nums

update #temp set pv = 0 where id = 1

loophere:
update #temp
set pv = calc
from #temp, ( select top 1 curr.ID, prev.pv + (11 + prev.b) - (10+prev.a) calc
from #temp curr inner join #temp prev on curr.id = prev.ID+1 where curr.pv is null ) thecalc
where #temp.ID = thecalc.ID
if @.@.ROWCOUNT <> 0 goto loophere

select * from #temp
drop table #temp

|||sweet.

Just a last note, on Blair last alternate solution is the need to evaluate the expression for the first row alone (if actually needed) since the loop does require to start in "recno 1" + 1 therefore missing its evaluation at row 1.|||the second line is the initial expr1:

update #temp set pv = 0 where id = 1

Previous Row Calculations

Hi all

I am wondering if anyone know a way that you can look up a value from the previous row to do a calculation on. I have a field that I need to subtract the same field from the previous row to validate. Is this possible in a query?

Thanks
KenzieOk, i just composed this script and i hope it could help you

WITH MyTable AS
(SELECT
*,
Num = ROW_NUMBER() OVER(ORDER BY <YourColumn>)
FROM [dbo].<YourTable>)

SELECT
tb.<YourColumn>,
tb.Num,
prev. <YourColumn>
FROM MyTable tb
LEFT JOIN
(SELECT
<YourColumn>,
Num = (ROW_NUMBER() OVER(ORDER BY <YourColumn>) -1)
FROM [dbo].<YourTable>) prev
ON tb.Num = prev.Num

Mind to change <YourColumn> and <YourTable>
let me know if it works (for me it's working)
|||Slight simplification, you can use only the CTE in the self-join like:

WITH t_seq
AS
(
select ROW_NUMBER() OVER(ORDER BY <column_list>) AS seq
from <table>
)
select ...
from t_seq AS t1
left join t_seq AS t2
on t2.seq = t1.seq -1;

previous row

Hi,
I am trying to do a query where I want to show not only the relevant results
but the previous line as well.
eg.
select *
where msg='crash'
I have a rowno in there but I am pretty new to this stuff so not real sure.
Thanks in advance
TimPerhaps you can give us some DDL and show us what you want to retrieve when
you issue am smaple command
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42916000$0$4656$afc38c87@.news.optusnet.com.au...
> Hi,
> I am trying to do a query where I want to show not only the relevant
> results
> but the previous line as well.
> eg.
> select *
> where msg='crash'
> I have a rowno in there but I am pretty new to this stuff so not real
> sure.
> Thanks in advance
> Tim
>|||ok, as I say I am pretty new to this so excuse any misunderstandings
columns: date msg type detail
rowno
11/5/06 info prog running
1
11/5/06 error prog crash
2
I basically want to be able to show the rowno previous to the prog crash
So I have,
Select *
from TABLENAME
where detail ='prog crash'
but I not sure how to do the next bit to retrieve the previous row.
thanks in advance
Tim
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:elnUUV1XFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Perhaps you can give us some DDL and show us what you want to retrieve
when
> you issue am smaple command
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
> news:42916000$0$4656$afc38c87@.news.optusnet.com.au...
>|||That would be one solution for that:
Select TOP 2 * from TABLENAME
Where Rowno <=
(
Select TOP 1 *
from TABLENAME
where detail ='prog crash'
order by rowno
)
Order by row no desc
I added the order by rowno if the detailcolumn is not precise enough,
perhaps mor ethan one coumn would fit that condition, saythose with 4 and
105, should then the rows 3 AND 104 be returned, or is the detail column
that precise only to return one row at a time ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42916a43$0$10304$afc38c87@.news.optusnet.com.au...
> ok, as I say I am pretty new to this so excuse any misunderstandings
> columns: date msg type detail
> rowno
> 11/5/06 info prog running
> 1
> 11/5/06 error prog crash
> 2
>
> I basically want to be able to show the rowno previous to the prog crash
> So I have,
> Select *
> from TABLENAME
> where detail ='prog crash'
> but I not sure how to do the next bit to retrieve the previous row.
> thanks in advance
> Tim
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:elnUUV1XFHA.1404@.TK2MSFTNGP09.phx.gbl...
> when
>|||yes would return 3 and 104 as well.
that query gives an error
'Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ecvwdn1XFHA.1384@.TK2MSFTNGP09.phx.gbl...
> That would be one solution for that:
> Select TOP 2 * from TABLENAME
> Where Rowno <=
> (
> Select TOP 1 *
> from TABLENAME
> where detail ='prog crash'
> order by rowno
> )
> Order by row no desc
> I added the order by rowno if the detailcolumn is not precise enough,
> perhaps mor ethan one coumn would fit that condition, saythose with 4 and
> 105, should then the rows 3 AND 104 be returned, or is the detail column
> that precise only to return one row at a time ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
> news:42916a43$0$10304$afc38c87@.news.optusnet.com.au...
>|||Ok, I setup a sample with the Northwind database which can be ported easily
for your case, try it and raise a hand if you stuck into it.
Select * from Orders O
Where CustomerID = 'VINET'
UNION
Select * from Orders
Where OrderID in
(
Select OrderID-1 from Orders O
Where CustomerID = 'VINET'
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42917894$0$9263$afc38c87@.news.optusnet.com.au...
> yes would return 3 and 104 as well.
> that query gives an error
> 'Only one expression can be specified in the select list when the subquery
> is not introduced with EXISTS'
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ecvwdn1XFHA.1384@.TK2MSFTNGP09.phx.gbl...
>|||You missed the most basic idea of the Relational Model. Tables have no
ordering. Rows are not "lines" or "records". Those exists in file
systems. What does this "rowno" mean in your data model, and where
does it occur in the reality you are modeling?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||>> I am pretty new to this so excuse any misunderstandings
Quite often people confuse the two dimensional representation of a table on
some visual medium ( a sheet of paper or a computer screen ) with the rows
sequentially arranged as if they can be navigated through like with a
recordset.
However, one of the basic tenets of relational databases is that, there is
no essential order associated with the rows in a table -- i.e. a row is
identified by <table_name, key_value> each and every value is identified by
the triplet <table_name, column_name, key_value). If the values in your
database cannot be identified this way, as a general rule, your design is
flawed. Therefore asking something about "previous line" does not really
make sense.
As a more specific suggestion, please read www.aspfaq.com/5006. Provide
DDLs, sample data & expected results for others to reproduce your problem
scenario.
Anith

previous postings

hello y'all!
does anybody know how i could browse all my previous postings here
particularly in sql server? i need something done which i know i already
posted it a few months ago.
thanks!
> does anybody know how i could browse all my previous postings here
> particularly in sql server? i need something done which i know i already
> posted it a few months ago.
http://groups.google.com/advanced_group_search?hl=en
|||hey scott, this is a great link
but what i need is to see all my previous postings here in MSDN sql server
newsgroups
thanks!
"Scott Morris" wrote:

> http://groups.google.com/advanced_group_search?hl=en
>
>
|||Under group just set it to : microsoft.public.sqlserver.server
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:41907E91-76A1-446D-9DAC-71159D3742B5@.microsoft.com...[vbcol=seagreen]
> hey scott, this is a great link
> but what i need is to see all my previous postings here in MSDN sql server
> newsgroups
> thanks!
> "Scott Morris" wrote:
|||What interface are you using? If you're using Outlook Express, cntl-H shows
you all your postings and all the replies to your postings.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:FB40B574-7A54-4A0F-B243-10AF5F5D05FB@.microsoft.com...
> hello y'all!
> does anybody know how i could browse all my previous postings here
> particularly in sql server? i need something done which i know i already
> posted it a few months ago.
> thanks!
>

previous postings

hello y'all!
does anybody know how i could browse all my previous postings here
particularly in sql server? i need something done which i know i already
posted it a few months ago.
thanks!> does anybody know how i could browse all my previous postings here
> particularly in sql server? i need something done which i know i already
> posted it a few months ago.
http://groups.google.com/advanced_group_search?hl=en|||hey scott, this is a great link
but what i need is to see all my previous postings here in MSDN sql server
newsgroups
thanks!
"Scott Morris" wrote:

> http://groups.google.com/advanced_group_search?hl=en
>
>|||Under group just set it to : microsoft.public.sqlserver.server
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:41907E91-76A1-446D-9DAC-71159D3742B5@.microsoft.com...[vbcol=seagreen]
> hey scott, this is a great link
> but what i need is to see all my previous postings here in MSDN sql server
> newsgroups
> thanks!
> "Scott Morris" wrote:
>|||What interface are you using? If you're using Outlook Express, cntl-H shows
you all your postings and all the replies to your postings.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:FB40B574-7A54-4A0F-B243-10AF5F5D05FB@.microsoft.com...
> hello y'all!
> does anybody know how i could browse all my previous postings here
> particularly in sql server? i need something done which i know i already
> posted it a few months ago.
> thanks!
>

previous postings

hello y'all!
does anybody know how i could browse all my previous postings here
particularly in sql server? i need something done which i know i already
posted it a few months ago.
thanks!> does anybody know how i could browse all my previous postings here
> particularly in sql server? i need something done which i know i already
> posted it a few months ago.
http://groups.google.com/advanced_group_search?hl=en|||hey scott, this is a great link
but what i need is to see all my previous postings here in MSDN sql server
newsgroups
thanks!
"Scott Morris" wrote:
> > does anybody know how i could browse all my previous postings here
> > particularly in sql server? i need something done which i know i already
> > posted it a few months ago.
> http://groups.google.com/advanced_group_search?hl=en
>
>|||Under group just set it to : microsoft.public.sqlserver.server
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:41907E91-76A1-446D-9DAC-71159D3742B5@.microsoft.com...
> hey scott, this is a great link
> but what i need is to see all my previous postings here in MSDN sql server
> newsgroups
> thanks!
> "Scott Morris" wrote:
>> > does anybody know how i could browse all my previous postings here
>> > particularly in sql server? i need something done which i know i
>> > already
>> > posted it a few months ago.
>> http://groups.google.com/advanced_group_search?hl=en
>>|||What interface are you using? If you're using Outlook Express, cntl-H shows
you all your postings and all the replies to your postings.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:FB40B574-7A54-4A0F-B243-10AF5F5D05FB@.microsoft.com...
> hello y'all!
> does anybody know how i could browse all my previous postings here
> particularly in sql server? i need something done which i know i already
> posted it a few months ago.
> thanks!
>

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))