Categories
- All Categories
- 89 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.1K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 50 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Start to till data metric calculation which is similar to YTD
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
-
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.
0 -
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.
0 -
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.
0 -
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.
0 -
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.
0 -
Yeap.
BTW I hadn't seen that this thread was still open...
0