Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Start to till data metric calculation which is similar to YTD

Received Response
11
Views
6
Comments
2845107
2845107 Rank 4 - Community Specialist

Hi Experts,

I am presently having YTD as on of my metric. I would like to have STD(Start to till date) "For example if YTD is giving me aggregated sum from starting of the year to till date, same way I would like my STD to work where it should consider all the previous years to till date aggregated sum.

So If I pull any dimension column,time dimension column, sales column and STD column my data should be as below.

Country     Year     Fact_Sales     Fact_Sales STD

America     2011     500                         3000

America     2012     500                         3000

America     2013     500                         3000

America     2014     500                         3000

America     2015     500                         3000

America     2016     500                         3000

France       2011     100                         600

France       2012     100                         600

France       2013     100                         600

France       2014     100                         600

France       2015     100                         600

France       2016     100                         600

To make it simple it should ignore the Year column and show respective to Country column. In report level we can do it, But since we need to do this for adhoc reporting and dimension changes as per requirement. Can you please suggest me a method where I can do this in RPD. So that it can sliced and diced with different dimensions and ignore the Year column.

Appreciate your help on this.

Answers

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    Well if you filter the time dimension and you create your STD (please change that abbreviation by the way!) as a time dimension grand total (So Dimension "Time" ==> Level "Grand Total") you will have precisely what you want. Not exactly what you ask for in terms of a measure but the same result.

    Because basically a calc like you ask doesn't really exist.

  • 2845107
    2845107 Rank 4 - Community Specialist

    Thanks for the reply.

    Ya doing level based measure at time grand total level will give me total sales till date. And hopefully it should repeat for all the years the same aggregated total sales value when I have year in my criteria.

    But when I include any dimension column like country and state in the criteria it will not slice and dice accordingly I feel.

    I need to try this and see. Thanks for posting an idea.

    Please correct me if I am wrong.

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru
    2845107 wrote:But when I include any dimension column like country and state in the criteria it will not slice and dice accordingly I feel.

    It's a time total so other dimensions will still react as they always do.

  • 2845107
    2845107 Rank 4 - Community Specialist

    I have created the level based mesaure "start to date" and set the level as grand total on it and the same is reflecting at the time grand total level.

    But I am not seeing the values at grand total level for each year the values are same as sales column only.

    For the this Fact sales's LTS I have time set content level as detailsfor time dimension, because I have my other metrics which needs to be shown at detail level like YTD and etc.

    Looks configuring like this is not working.

    Do I need to make any other changes to make this work.

    Please suggest.

  • 2845107
    2845107 Rank 4 - Community Specialist

    Thanks Christian for the inputs.

    What worked for me is... I have created a new alias of the fact table (Fact Sales) and kept all the joins same as my original but not joining to the time dimension. I have defined the content level for the all the joined dimension to detail and the time dimension which is not joined to this new aliased fact table I have kept it as total. And also total at the metric level.

    This way if i slice and dice with any other dimension by pulling my "Start to Date" from new fact. It will show the total aggregated value irrespective of other dimensions.

    Thanks.

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    Yeap.

    BTW I hadn't seen that this thread was still open...