assuming you have access to the rpd my preferred method for your requirement would be to; -
Use AGO functionality to build your new measures for last period as a logical function
Create a new logical measure that uses your two time series measures as the basis of your X - Y calculation
If I have not described your requirement then kindly clarify with more detail.
Thanks for the update but I kind use ago function on my existing measure PROJTD_BILLING, the reason is in PROJTD_BILLING I am passing "ALL_YEAR" level, so it will include all data set.
So when i am using ago with same level there is no data set that wil not return any value.
And i cant use ago with different level as obiee doesn't accept it, it show error.
Okay, you have to use the AGO measures with a period that pins them to what they are AGO relative to, BUT, you could solve this from the other direction by creating a FULL YEAR measure by duplicating the base member and pinning it to YEAR or TOTAL level (I am not clear what ALL_YEAR means, if it is full year then the former, if all years then the latter) on the time dimension hierarchy.
This then makes that measure invariant below the level of detail you have pinned it to, so if TOTAL it will show the sum of all years, if YEAR it will give the total for the year relative to the detail being shown (so for this month the sum of 2017, assuming Gregorian calendar in your time dimension)
1 person found this helpful
Your examples is weird ... It works except for the end:
2017-01: billing = NULL, to date billing = 25
2017-02: billing = 35, to date billing = 40
25 + 35 is supposed to give you 60 as to date billing for 2017-02
So if you explain how your to date can be 40 instead of 60 then it will be possible to solve your question.
Assuming it's really 60 (and the 40 is a mistake) than what you look for is just exactly the same as the billing of the current period (where NULLs are 0) with a changed signed.
Because (TODATE of 2017-01) - (TODATE of 2017-02) = (value of 2017-02) * -1
And your example of that column is also wrong ....
Or there is something you aren't saying and formulas aren't really as described and the example is just wrong in few places.
Thanks for your time.
But in my case ALL_YEARS means all year not a particular year.
And whenever I want to use ago function with above already created measure "PROJTD_BILLING" (calculated for all_year),it will not left with any dataset.
Hope I am clear!!!.
You are clear, then as I suggested pin a copy (drag / drop or copy it on to the hierarchy level) of your base measure to TOTAL in your time dimension hierarchy and rename it as appropriate, that will do what you describe, and can then be used in conjunction with time series measures, but the time series measures still need time detail in the analysis to work, but this will not effect the result that the pinned measure shows, it will not change as the years / months / days do.
Thanks for the update
You are correct
25 + 35 is supposed to give you 60 as to date billing for 2017-02.
And I also want calculate current period billing only but not directly as you mention.
(TODATE of 2017-01) - (TODATE of 2017-02) = (value of 2017-02) * -1
I want it from already calculate column "PROJTD_BILLING" to avoid some rounding and other problem.
Any help on this really appreciated.
Yes but as I already mention ago on existing measure will not give me any data because there no data set left for ago.As i already using level all_year(means all year from project started to current period)
in column "PROJTD_BILLING".
So AGO("PROJTD_BILLING","ALL_YEARS",1) will not result any data.
Hope this is more clear.
AGO ALL YEARS makes no sense (give me one period before everything?!)
Your time period based measures need to reference the base measure.
So you need the base measure to use generally, and to use as the basis of your time period.
You need a copy, which is pinned to TOTAL_YEAR and so always gives the same number.
You can then create as many logical measures as you like that reference the above as part of their formula.
Thanks for the update.
Can you please provide some example or more explanation.
Thanks in advance.
1 person found this helpful
Hard to read your mind on what you don't understand but here goes; -
If you have a Total year measure then it must be based on either an ETL process populating an all year number, which does not seem likely, or a value that is aggregated and has its detail level set to the total level of your time hierarchical dimension.
Assuming it is the latter you need to copy it and have its copy set to the detail level of the time hierarchy.
Then you can create a new time series measure on this, that will use the AGO, YTD or whatever.
Then you will have the Year Total measure (as you do now), a detail equivalent that will only relate to the relevant time period, and a time series measure which will work, provided you have time in your analysis.
Finally you can then build a logical measure which makes the A - B functionality that you alluded to in your original question.
If this still does not make sense then please provide precise detail of what you are not understanding.
Note - I am saying to do all of the above on the RPD, business model layer (and presentation!)
Thanks for your support.
I have fixed my issue with another work around.