How to use YTD on a mirror period

Hi friends

I have a funny situation. I have a period dimension which contains Year, Qtr, Month. I also have another Time dimension which contains Mth1 .. Mth25.

The 2nd Time dimension matches that of the 1st Time dimension ie. Mth25 is 2005.April. and Mth24 is 2005.March and so on...

However I need to calculate YTD when MTH25 is selected from the dimension.

I can't exactly do YTD (Mth25) as Mth25 is not a time dimension. Is there a way to Retrieve the equivalent time dimension in the form of 2005.April from Mth25 so that I can use in YTD Calculation ?

Thanks

Tom

Do you have a property saying the month?
If so you can do YTD(STRTOMEMBER("[Time].[Month].["+[MthDim].CURRENTMEMBER.PROPERTIES("Month Name")+"]"))
I hope it helps you.|||Thinking out loud a bit (i.e., have not tried this), but you might be able to do something like this. Use the Rank() function to determine what month number you have within the current member's siblings:

Rank([MthDim].CurrentMember, [MthDim].CurrentMember.Siblings)

This should return 1 for Mth1, 14 for Mth14, and so on.

Then, use this with the Item() function (substracting 1 given that Rank() returns a 1-based position while Item() expects a 0-based position) to decide which member in the time dimension to return:

Descendents(StrToMember("[Time].[Year].[" + [MthDim].CurrentMember.Parent.Name + "]"), [Time].[Month]).Item(<rank function> - 1)

This assumes you have a [Year] level in [MthDim] as well as [Time]. Might work...

Dave Fackler
|||That was helpful... however when this is done

sum(YTD(StrToMember("[Period2].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Year") + "].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Quarter") + "].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Month") + "]"
)), [Measures].[Unit] )

Where Period2 is Month1 .. 25
where Period is Year, Quarter, Month

the sum returned is not for Year To Date ie. April figure + March Figure + Feb.. + Jan..

When I pivot to Month 25 in Period2 which is 2005-April. The value thats displayed is ONLY the figure for APRIL instead of April figure + March Figure + Feb.. + Jan..

do you have any idea why this is the case ?

Thanks
Tom|||sorry made a mistake in the above post

Where Period2 is Month1 .. 25
where Period is Year, Quarter, Month

is actually

Where Period2 is Year, Quarter, Month
where Period is Month1 .. 25 but contains Year, Quarter, Month as properties.

So, sorry but just to repeat the problem is still there if anyone could help I'd appreciate it

Thanks
Tom
|||I think I know.. Because we're returning a Member using StrToMember instead maybe we should use StrToSet. Because we're only returning a member when YTD is performed it is only calculating that member.

Maybe we should use StrToSet ?

though I'm not entirely show how the expression should go.

any help is appreciated

Thankyou
Tom|||Hi Tom,

YTD() does want a member, so I believe using StrToMember is the right approach. Does your [Period] dimension (the one with Mth1...Mth25) contain a [Year] level? I am assuming it does since it sounds as if your time span is greater than one year (since [Period2] has [Year], [Quarter], etc.). If this is the case, you need to make sure the dimension is marked as a "Time" dimension (property of the dimension) and the [Year] level is marked as a "Years" level (property of the level).

The YTD(<member>) function is a shortcut for the PeriodsToDate([Year], <member>) function. The YTD() function, however, needs to be able to determine what your [Year] level is. It does this using the properties of the levels in the dimension to determine which one has been marked as "Years". This allows the YTD() function to know the year parent for the <member> in question and then correctly aggregate all the members from the first child under that year up through the <member> specified.

Dave Fackler
|||

Hi david

You're right YTD() only take member.

The [Period] dimension with Mth1..25 is set as a Time Type and it also contains a [Year] level which currently is hidden with visible set to false and it is marked as a Years level.

Something strange that I've noticed is that the below query only returns the YTD for the month [Period2].&[2005].&[2].&[4], instead of from [Period2].&[2005].&[1].&[1] + [Period2].&[2005].&[1].&[2] + [Period2].&[2005].&[1].&[3] +
[Period2].&[2005].&[2].&[1]...

I think the issue lies here. what is the difference between

1. sum(ytd([Period2].&[2005].&[2].&[4]), [Measures].[Unit])

and

2. sum(ytd([Period2].currentmember), [Measures].[Unit])
2. which when pivoted to 2005 april returns the ytd total of jan + feb+ march +april which is what i want.

however 1. only returns the unit for [Period2].&[2005].&[2].&[4] only

Thanks alot for your help !
Tom

|||I've really ran out of ideas.

Is there another way to do this ? surely it shouldn't be this difficult to do something like it ?

:) any suggestions

Thanks
Tom

how to use the minus operator

hi...i can up into a situation where i have to use the MINUS operator
but i don't find that in sql...is there any alternatives,,,i tried NOT
IN but it just returned me one column and i have multiple changes in
my two tables like
CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
emp dept
a 01
a 02
b 01
c 04
CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
emp dept
a 01
b 04
d 05
output table:
emp dept
a 02
b 04
d 05
emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
emp 'd' is added. I need all the changed in the table. thanks for
sharing..."sql_learner" <mailfrd@.gmail.com> wrote in message
news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
> hi...i can up into a situation where i have to use the MINUS operator
> but i don't find that in sql...is there any alternatives,,,i tried NOT
> IN but it just returned me one column and i have multiple changes in
> my two tables like
> CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> emp dept
> a 01
> a 02
> b 01
> c 04
> CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> emp dept
> a 01
> b 04
> d 05
> output table:
> emp dept
> a 02
> b 04
> d 05
> emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
> emp 'd' is added. I need all the changed in the table. thanks for
> sharing...
SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
DIFFERENCE - as in Oracle for example).
I can't work out what that has to do with the result you asked for. If
EXCEPT doesn't answer your question then maybe you could explain the logic
you are tring to apply.
--
David Portas|||i am just looking for all the changes that exists in both the
tables...i tried
select * from t1 a
left outer join
t2 b on (a.emp=b.emp and a.dept=b.dept)
where b.emp is null
but this gives me only the changes that is in the second table t2 and
not the other way round. i can again use right join and finally join
them but i was hoping there might be a better way to find all the
changes there are. and also comparing all the columns is little
clumsy..what if there is a lot of tables...so if u know something
that could do this then please help me...thanks|||On Dec 19, 5:20 pm, babu.dah...@.gmail.com wrote:
> i am just looking for all the changes that exists in both the
> tables...i tried
> select * from t1 a
> left outer join
> t2 b on (a.emp=b.emp and a.dept=b.dept)
> where b.emp is null
> but this gives me only the changes that is in the second table t2 and
> not the other way round. i can again use right join and finally join
> them but i was hoping there might be a better way to find all the
> changes there are. and also comparing all the columns is little
> clumsy..what if there is a lot of tables...so if u know something
> that could do this then please help me...thanks
my mistake...i meant "what if there are a lot of COLUMNS not tables"
in my previous post|||On Dec 19, 5:05 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "sql_learner" <mail...@.gmail.com> wrote in message
> news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
>
> > hi...i can up into a situation where i have to use the MINUS operator
> > but i don't find that in sql...is there any alternatives,,,i tried NOT
> > IN but it just returned me one column and i have multiple changes in
> > my two tables like
> > CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> > emp dept
> > a 01
> > a 02
> > b 01
> > c 04
> > CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> > emp dept
> > a 01
> > b 04
> > d 05
> > output table:
> > emp dept
> > a 02
> > b 04
> > d 05
> > emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
> > emp 'd' is added. I need all the changed in the table. thanks for
> > sharing...
> SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
> DIFFERENCE - as in Oracle for example).
> I can't work out what that has to do with the result you asked for. If
> EXCEPT doesn't answer your question then maybe you could explain the logic
> you are tring to apply.
> --
> David Portas
like mr. babu.dah...@.gmail.com said..i used join, not exists but both
gives changes in one table and not both...i wanted to try EXCEPT but
it is not taking it...i don't know what is wrong...i tried
select * from t1
except (select * from t2)
and also
select emp from t1
except (select emp from t2)
please correct me with my syntax...thanks|||This should work for you
SELECT emp,dept
FROM emp1
WHERE NOT EXISTS
(SELECT 1 FROM emp2 WHERE emp1.empname = emp2.empname and emp1.dept =emp2.dept)
- Sha Anand
"sql_learner" wrote:
> On Dec 19, 5:05 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> > "sql_learner" <mail...@.gmail.com> wrote in message
> >
> > news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
> >
> >
> >
> > > hi...i can up into a situation where i have to use the MINUS operator
> > > but i don't find that in sql...is there any alternatives,,,i tried NOT
> > > IN but it just returned me one column and i have multiple changes in
> > > my two tables like
> >
> > > CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> >
> > > emp dept
> > > a 01
> > > a 02
> > > b 01
> > > c 04
> >
> > > CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> >
> > > emp dept
> > > a 01
> > > b 04
> > > d 05
> >
> > > output table:
> >
> > > emp dept
> > > a 02
> > > b 04
> > > d 05
> >
> > > emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
> > > emp 'd' is added. I need all the changed in the table. thanks for
> > > sharing...
> >
> > SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
> > DIFFERENCE - as in Oracle for example).
> >
> > I can't work out what that has to do with the result you asked for. If
> > EXCEPT doesn't answer your question then maybe you could explain the logic
> > you are tring to apply.
> >
> > --
> > David Portas
> like mr. babu.dah...@.gmail.com said..i used join, not exists but both
> gives changes in one table and not both...i wanted to try EXCEPT but
> it is not taking it...i don't know what is wrong...i tried
> select * from t1
> except (select * from t2)
> and also
> select emp from t1
> except (select emp from t2)
> please correct me with my syntax...thanks
>|||select * from t1 a
where not exists (select * from t2 b where a.emp=b.emp and
a.dept=b.dept)
union all
select * from t2 a
where not exists (select * from t1 b where a.emp=b.emp and
a.dept=b.dept)
this will give u all the changes in both the tables..|||select *
from t1 a
full outer join
t2 b on (a.emp=b.emp and a.dept=b.dept)
where b.emp is null
should give you both sides
"babu.dahal1@.gmail.com" wrote:
> i am just looking for all the changes that exists in both the
> tables...i tried
> select * from t1 a
> left outer join
> t2 b on (a.emp=b.emp and a.dept=b.dept)
> where b.emp is null
> but this gives me only the changes that is in the second table t2 and
> not the other way round. i can again use right join and finally join
> them but i was hoping there might be a better way to find all the
> changes there are. and also comparing all the columns is little
> clumsy..what if there is a lot of tables...so if u know something
> that could do this then please help me...thanks
>sql

how to use the minus operator

hi...i can up into a situation where i have to use the MINUS operator
but i don't find that in sql...is there any alternatives,,,i tried NOT
IN but it just returned me one column and i have multiple changes in
my two tables like
CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
emp dept
a 01
a 02
b 01
c 04
CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
emp dept
a 01
b 04
d 05
output table:
emp dept
a 02
b 04
d 05
emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
emp 'd' is added. I need all the changed in the table. thanks for
sharing..."sql_learner" <mailfrd@.gmail.com> wrote in message
news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
> hi...i can up into a situation where i have to use the MINUS operator
> but i don't find that in sql...is there any alternatives,,,i tried NOT
> IN but it just returned me one column and i have multiple changes in
> my two tables like
> CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> emp dept
> a 01
> a 02
> b 01
> c 04
> CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> emp dept
> a 01
> b 04
> d 05
> output table:
> emp dept
> a 02
> b 04
> d 05
> emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
> emp 'd' is added. I need all the changed in the table. thanks for
> sharing...
SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
DIFFERENCE - as in Oracle for example).
I can't work out what that has to do with the result you asked for. If
EXCEPT doesn't answer your question then maybe you could explain the logic
you are tring to apply.
David Portas|||i am just looking for all the changes that exists in both the
tables...i tried
select * from t1 a
left outer join
t2 b on (a.emp=b.emp and a.dept=b.dept)
where b.emp is null
but this gives me only the changes that is in the second table t2 and
not the other way round. i can again use right join and finally join
them but i was hoping there might be a better way to find all the
changes there are. and also comparing all the columns is little
clumsy..what if there is a lot of tables...so if u know something
that could do this then please help me...thanks|||On Dec 19, 5:20 pm, babu.dah...@.gmail.com wrote:
> i am just looking for all the changes that exists in both the
> tables...i tried
> select * from t1 a
> left outer join
> t2 b on (a.emp=b.emp and a.dept=b.dept)
> where b.emp is null
> but this gives me only the changes that is in the second table t2 and
> not the other way round. i can again use right join and finally join
> them but i was hoping there might be a better way to find all the
> changes there are. and also comparing all the columns is little
> clumsy..what if there is a lot of tables...so if u know something
> that could do this then please help me...thanks
my mistake...i meant "what if there are a lot of COLUMNS not tables"
in my previous post|||On Dec 19, 5:05 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "sql_learner" <mail...@.gmail.com> wrote in message
> news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
>
>
>
>
>
>
>
>
>
> SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
> DIFFERENCE - as in Oracle for example).
> I can't work out what that has to do with the result you asked for. If
> EXCEPT doesn't answer your question then maybe you could explain the logic
> you are tring to apply.
> --
> David Portas
like mr. babu.dah...@.gmail.com said..i used join, not exists but both
gives changes in one table and not both...i wanted to try EXCEPT but
it is not taking it...i don't know what is wrong...i tried
select * from t1
except (select * from t2)
and also
select emp from t1
except (select emp from t2)
please correct me with my syntax...thanks|||This should work for you
SELECT emp,dept
FROM emp1
WHERE NOT EXISTS
(SELECT 1 FROM emp2 WHERE emp1.empname = emp2.empname and emp1.dept =
emp2.dept)
- Sha Anand
"sql_learner" wrote:

> On Dec 19, 5:05 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> like mr. babu.dah...@.gmail.com said..i used join, not exists but both
> gives changes in one table and not both...i wanted to try EXCEPT but
> it is not taking it...i don't know what is wrong...i tried
> select * from t1
> except (select * from t2)
> and also
> select emp from t1
> except (select emp from t2)
> please correct me with my syntax...thanks
>|||select * from t1 a
where not exists (select * from t2 b where a.emp=b.emp and
a.dept=b.dept)
union all
select * from t2 a
where not exists (select * from t1 b where a.emp=b.emp and
a.dept=b.dept)
this will give u all the changes in both the tables..|||select *
from t1 a
full outer join
t2 b on (a.emp=b.emp and a.dept=b.dept)
where b.emp is null
should give you both sides
"babu.dahal1@.gmail.com" wrote:

> i am just looking for all the changes that exists in both the
> tables...i tried
> select * from t1 a
> left outer join
> t2 b on (a.emp=b.emp and a.dept=b.dept)
> where b.emp is null
> but this gives me only the changes that is in the second table t2 and
> not the other way round. i can again use right join and finally join
> them but i was hoping there might be a better way to find all the
> changes there are. and also comparing all the columns is little
> clumsy..what if there is a lot of tables...so if u know something
> that could do this then please help me...thanks
>