This content has been marked as final. Show 6 replies
The formula which you are used for the last year YTD will calculate the period from jan/11 to dec/11.
YTD:Year-to-date:It means the sum of measure value of the complete year.If it is a current year then it will calculate to till current month.
mark if helpful/correct..
I understand that but how to calculate the sum of 2011/Jan to 2011/Apr for Last yr based on the month selected from the prompt.
Any help / guidance would be highly appreciated.
This is not something you can do in the repository.1 person found this helpful
You could create a filter (calculation that is) to remove any month > your_prompt, but this will require that your month is a date or timestamp value and not a varchar equivalent, or that you convert any user friendly text dates into a date / timestamp to do this.
Alternatively case when sum(date < your_prompt then your_balance else 0 end)
As per your advise , I am trying to create the following script in teh fact expression. But it is not giving correct value.
CASE WHEN "Core"."Dim - Date Fiscal Calendar"."Fiscal Year Number" = VALUEOF(NQ_SESSION.CURRENT_YEAR)-1 and "Core"."Dim - Date Fiscal Calendar"."Calendar Month Number" <= VALUEOF(NQ_SESSION.CURRENT_MONTH) THEN "Core"."Fact - Fins - GL Other"."US Eq Amount"
Obvioulsy I can use Request variable to dynamically chnage the values of the variables as per the user prompted value.
Any help here pls.
This is where I would get tricky. In the physical layer I'd create an alias to your fact table called FACT_YEAR_AGO, and I'd join it to the time dimension using year = year - 1 or whatever is similar in your schema. Then "Year Ago" metrics would just source from the alias fact table instead of the original fact table, and "Year Ago YTD" would simply use a YTD formula against the alias table.
Hope this helps, easier to show than to describe in email.