×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to modify this WTD Transformation?

How to modify this WTD Transformation?

How to modify this WTD Transformation?

(OP)
Report has fallowing objects on it
Store ..........attribute,
Revenue, WTD Rev, MTD Rev, YTD Rev.....Metrics
Yesterday, last week, Choose month, Trailing four weeks (filters)......Prompt

I am calculating WTD, MTD, and YTD using transformation tables each has two colums (ex:day and wtd_day_date) . Base fact table has data at day level. I am able to get the right data when I select Yesterday filter from prompt. But I am getting same data for all the metrics when I select any other filters.

Sample sql when I select Yesterday filter which is right:

select    a11.StoreId  STORE_ID,
    sum((a11.[Sale Dollars] + a11.[Return Dollars]))  WJXBFS1
into #ZZMD00
from    vw_metric    a11
where    a11.DATE_ID = CONVERT(datetime, '2008-08-18 00:00:00', 120)
group by    a11.StoreId

select    a11.StoreId  STORE_ID,
    sum((a11.[Sale Dollars] + a11.[Return Dollars]))  WJXBFS1
into #ZZMD01
from    vw_metric    a11
    join    Date_WTD    a12
      on     (a11.DATE_ID = a12.WTD_DATE_ID)
where    a12.DATE_ID = CONVERT(datetime, '2008-08-18 00:00:00', 120)
group by    a11.StoreId

Sample sql when I select Lase week filter(doing the same for all metrics):

select    a11.StoreId  STORE_ID,
    sum((a11.[Sale Dollars] + a11.[Return Dollars]))  WJXBFS1
into #ZZMD00
from    vw_metric    a11
    join    L_DATE    a12
      on     (a11.DATE_ID = a12.DATE_ID)
where    a12.Week_ID = CONVERT(datetime, '2008-08-16 00:00:00', 120)
group by    a11.StoreId

select    a11.StoreId  STORE_ID,
    sum((a11.[Sale Dollars] + a11.[Return Dollars]))  WJXBFS1
into #ZZMD01
from    vw_metric    a11
    join    L_DATE    a12
      on     (a11.DATE_ID = a12.DATE_ID)
where    a12.Week_ID = CONVERT(datetime, '2008-08-16 00:00:00', 120)
group by    a11.StoreId

Can some please help me solve this metric.
Thanks in advance.
 

RE: How to modify this WTD Transformation?

Your problem is probably because your transformation has the relationship defined at the day level only and you are filtering on week. Try adding day/date dimensionality to your metric with ending lookup and it should use the last day of the week when writing the query for your metric.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close