locked
Measure Between Dates RRS feed

  • Question

  • I have a campaign dimension ( Its a marketing campaign for a product ) and i have to measure the sales that happened during the campaign , Each campaign will have a duration ( start date and end date ) 

    For Example : a sample record in the campaign dimension looks like this 

    CampaignKey : 1 

    CampaignName: XXXX 

    Cam[aignStartDate :01-01-2012 

    CampaignEndDate :11-01-2012

    StartKey : 74561 ( refers to the primary key in time dimension ) 

    EndKey :74667 ( refers to the primary key in time dimension ) 

    i need to analyse the total sales that happened during a campaign , the cube shouldbe able to get the sales figure that happened between the start date and the end date for the given campaign ( i also have a time key in the fact table ) 

    To do this i have added campaign key to the fact table and got the result.But in my campaign table there are also columns precampaigndate and postcampaigndate which are campaigndate-duration and campaigndate+duration respectively.

    Now i want sales happend between precampaigndate to startdate and sales happend between postcampaigndate and enddate.

    Could someone please help me out how to do this??


    Wednesday, January 16, 2013 9:14 AM

Answers

  • Hi Aruna,

    There are many ways of achieving this.  Just one way is

    Your campaign can have two properties, StartDate and EndDate.  Then in your calculation you can sum the sales between the two dates along these lines.  You will need to be able to convert StartDate and EndDate to the keys of date. ie

    sum(strtomember("[Date].[Date].&["+Campaign.Campaign.properties("StartDate"))+"]:["

    +strtomember("[Date].[Date].&["+Campaign.Campaign.properties("EndDate")+"]"),Measures.Sales)

    As Raunak mentions, the more you know about mdx, the more options you will have.

    Hope that helps,

    Richard 

    http://RichardLees.blogspot.com 


    Richard

    • Proposed as answer by RichardLees Thursday, January 17, 2013 5:08 AM
    • Marked as answer by Elvis Long Monday, January 28, 2013 2:32 AM
    Thursday, January 17, 2013 5:08 AM

All replies

  • I have a campaign dimension ( Its a marketing campaign for a product ) and i have to measure the sales that happened during the campaign , Each campaign will have a duration ( start date and end date ) 

    For Example : a sample record in the campaign dimension looks like this 

    CampaignKey : 1 

    CampaignName: XXXX 

    Cam[aignStartDate :01-01-2012 

    CampaignEndDate :11-01-2012

    StartKey : 74561 ( refers to the primary key in time dimension ) 

    EndKey :74667 ( refers to the primary key in time dimension ) 

    i need to analyse the total sales that happened during a campaign , the cube shouldbe able to get the sales figure that happened between the start date and the end date for the given campaign ( i also have a time key in the fact table ) 

    To do this i have added campaign key to the fact table and got the result.But in my campaign table there are also columns precampaigndate and postcampaigndate which are campaigndate-duration and campaigndate+duration respectively.

    Now i want sales happend between precampaigndate to startdate and sales happend between postcampaigndate and enddate.

    Could someone please hepl me out how to do this??


    • Merged by Elvis Long Friday, January 18, 2013 8:14 AM duplicate
    Wednesday, January 16, 2013 9:14 AM
  • How comfortable you are writing MDX to query the SSAS cube?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Wednesday, January 16, 2013 9:57 AM
  • i'm in twilight zone? how can that question be 100% identical to this posted under different name?:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/a2d6811d-72c3-4553-9b8a-22d8779977db

    Wednesday, January 16, 2013 12:33 PM
  • we both are working in same team..:)
    Wednesday, January 16, 2013 12:53 PM
  • Hi Aruna,

    There are many ways of achieving this.  Just one way is

    Your campaign can have two properties, StartDate and EndDate.  Then in your calculation you can sum the sales between the two dates along these lines.  You will need to be able to convert StartDate and EndDate to the keys of date. ie

    sum(strtomember("[Date].[Date].&["+Campaign.Campaign.properties("StartDate"))+"]:["

    +strtomember("[Date].[Date].&["+Campaign.Campaign.properties("EndDate")+"]"),Measures.Sales)

    As Raunak mentions, the more you know about mdx, the more options you will have.

    Hope that helps,

    Richard 

    http://RichardLees.blogspot.com 


    Richard

    • Proposed as answer by RichardLees Thursday, January 17, 2013 5:08 AM
    • Marked as answer by Elvis Long Monday, January 28, 2013 2:32 AM
    Thursday, January 17, 2013 5:08 AM
  • Hi Richard,

    Thanks for your solution.Will try to implement this and learn more about writing MdX expressions.

    Im just a fresher and started working on cube a few weeks back.

    Friday, January 18, 2013 10:59 AM