how to calculate prior period with 52 weeks ?

I defined the calc for prior period for sales amount. But is it possible to get last 52 weeks from the current period?

how to define in this calc?

-- PRIOR PERIOD CALCULATIONS

([Time Calculations].[Prior Period]=

IIF(([Date].[Fiscal Hierarchy].currentmember.lag(1),

[Time Calculations].&[ Current Period])=0,null,

([Date].[Fiscal Hierarchy].currentmember.lag(1),

[Time Calculations].&[ Current Period]))

);

If your hierarchy has a week-level, then I believe what you are looking for is this:

Code Snippet

SET [Time Calculations].[Prior Period] AS 'LastPeriods( 52, [Date].[Fiscal Hierarchy].currentmember )

This gives you a set of the current week and the previous 52 weeks. Assuming [Date].[Fiscal Hierarchy].currentmember is at week-level.

Best regards

- Jens

how to calculate percentage

b/w two colums?
i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanksjust do select table1.column1/table2.column2 from table1,table2

are the tables related in anyways? are they just numeric values that have nothing to do with each other? i need more information to help|||yes they are related and numberic
it gives me 0{zero] through in the results

i did table1.column1/table2.column2 as Percentage from table1,table2
do i have to create column? why outputing 0's only?|||Probably one or both columns are declared as integer. Try:

select convert(numeric(38, 19), table1.column1)/convert(numeric(38, 19), table2.column2) from table1,table2|||error
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.table1' does not match with a table name or alias name used in the query.

this column name has space and im using it like

select convert(numeric(38, 19), table1.[column1 name])/convert(numeric(38, 19), table2.column2)|||i just ran it and it worked fine

select convert(numeric(38,19), table1.[a c])/convert(numeric(38,19), table2.[b d])
from table1, table2

i guess double check that you are using the right DB, and you've typed everything correctly. Otherwise can you post up your query for me to look at?|||thks
it's working|||do you know how to format this to 2 decimal places?
like 20 instead of 0.02|||Umm...I'll just take a guess here...multiply by 100?

I assume you meant that 0.02 should display as 2, not 20...

How to calculate number of occurances of a character in a string

Is there a string function in T-sql which tells us that the number of occurances of a character in a string?
eg. abracadabra
no. of a's in the string : 5
Any Help is appreciated.select len(@.YourString)- len(replace(@.YourString, 'a', ''))

blindman

how to calculate hours between 2 dates but excluding weekends ?

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened
and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
Thanks!No easy way! I have to do something like this myself next week.
You have to decide whether this is going to be done in SQL or VB. I'd
suggest VB will be the easiest to code and depending on the volumes of
data involved may be best.
So assuming VB, I'd suggest the datediff function you have, then work
out how many weekends in the daterange, you can use the weekday
function to tell what day you are starting on, subtract the number of
weekends * 48 and you'll be someway towards it.
--
Regards
Chris
agenda9533 wrote:
> Looking for way&logic to calculate hours between 2 dates
> (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!|||See my reply to your previous post.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:5085DC03-FEE0-4934-9EFE-12BDE708F139@.microsoft.com...
> Looking for way&logic to calculate hours between 2 dates
> (Ticket.date_opened
> and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!
>|||Perhaps some T-SQL?
I plan on trying this out when I get the time:
http://www.aspfaq.com/show.asp?id=2453
No holidays though!
agenda9533 wrote:
> Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened
> and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!
>

How to calculate hours between 2 dates but excluding weekends ?

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened
and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
Thanks!You need to create a custom VB.NET function that takes the two dates, sth
like:
Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
Saturday has actually fully passed by
Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear, d1,
d2, FirstDayOfWeek.Monday)
Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
SundaysPassed) * 24
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
> Looking for way&logic to calculate hours between 2 dates
> (Ticket.date_opened
> and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!
>|||Wow! Thanks a lot!
"Teo Lachev [MVP]" wrote:
> You need to create a custom VB.NET function that takes the two dates, sth
> like:
> Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
> Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
> d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
> Saturday has actually fully passed by
> Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear, d1,
> d2, FirstDayOfWeek.Monday)
> Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
> SundaysPassed) * 24
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
> news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
> > Looking for way&logic to calculate hours between 2 dates
> > (Ticket.date_opened
> > and Ticket.solved_date) but excluding weekends ?
> > This function calculates hours only
> > DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> >
> > Thanks!
> >
>
>|||How to declare function?
How to pass date_opened and last_updated values?
Should it be report fields - Fields!date_opened.Value,
Fields!last_updated.Value?
"Teo Lachev [MVP]" wrote:
> You need to create a custom VB.NET function that takes the two dates, sth
> like:
> Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
> Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
> d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
> Saturday has actually fully passed by
> Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear, d1,
> d2, FirstDayOfWeek.Monday)
> Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
> SundaysPassed) * 24
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
> news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
> > Looking for way&logic to calculate hours between 2 dates
> > (Ticket.date_opened
> > and Ticket.solved_date) but excluding weekends ?
> > This function calculates hours only
> > DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> >
> > Thanks!
> >
>
>|||You create either embedded funtion in your report in VB.net or an external
assembly. From the field that needs to be validated, you pass the date
fields as input parameters. You may find the beginning of this article
helpful to get you started
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:9CA39B8B-FD4B-42C9-8C21-1B78168602DB@.microsoft.com...
> How to declare function?
> How to pass date_opened and last_updated values?
> Should it be report fields - Fields!date_opened.Value,
> Fields!last_updated.Value?
> "Teo Lachev [MVP]" wrote:
>> You need to create a custom VB.NET function that takes the two dates, sth
>> like:
>> Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
>> Dim SaturdaysPassed As Integer =>> DateDiff(DateInterval.WeekOfYear,
>> d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
>> Saturday has actually fully passed by
>> Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
>> d1,
>> d2, FirstDayOfWeek.Monday)
>> Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
>> SundaysPassed) * 24
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
>> news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
>> > Looking for way&logic to calculate hours between 2 dates
>> > (Ticket.date_opened
>> > and Ticket.solved_date) but excluding weekends ?
>> > This function calculates hours only
>> > DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
>> >
>> > Thanks!
>> >
>>

How to calculate hours between 2 dates but excluding weekends

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).

Thanks!I suggest you create a function to calculate the number of weekdays between 2 dates (excluding the weekends using the datepart(dw,@.date)-function) and multiplying this by 24...

or alternativly,
calculating the weekend-days, multiplying this by 24 and subtract the datediff-result with this value
this will only be correct if you use dates which are no weekenddays themselfs ofcourse...

How to calculate hours between 2 dates but excluding weekends

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).

Thanks!I suggest you create a function to calculate the number of weekdays between 2 dates (excluding the weekends using the datepart(dw,@.date)-function) and multiplying this by 24...

or alternativly,
calculating the weekend-days, multiplying this by 24 and subtract the datediff-result with this value
this will only be correct if you use dates which are no weekenddays themselfs ofcourse...