how to create last Year same month and ytm Value

[Former Member]

Hi all

I have three year s data in the table ,

I have a prompt on the cal year/Month, but the thing is i want to calculate the Last year same month value and ytm value s ,take kpi as(Actual)

But the thing is we need to calculate these value s in the report level.

I dont want to create these variables in the universe level.

thanks

santhosh

[Former Member · Accepted solution]

at the webi level create two variables:

v1(extracts year and converts it to numeric): to_number(substring(<year/month>;1,4))

v2(extracts month): substring(<year/month>;6,12)

create a 3rd variable to write the previous year logic like this:

v3: if(ispromptanswered('enter year/month')=yes;([v1]-1'/'v2);<year/month>)

use v3 in ur logic to build table against previous year

Edited by: kbharadwaj79 on Jun 3, 2011 5:37 PM

[Former Member · Accepted solution]

Hello ,

But we cant use the table and column refrences in the report level right?

Then how can we achive this ?

Thanks

Santhosh V

[Former Member · Accepted solution]

Santhosh Vudarla,

all the functions used in my previous post can be acheived at the reoprt level by creating variables in the webi. As long as you have 'year/month' object available in the report level.

Thanks,

Karthik

[Former Member · Accepted solution]

Santhosh, why do you need to create them at the report level? You will need to create them in the report every single time someone wants to report on this.

However, given that you do and you are prompting for year month, then let's take it from there.

You need a UserResponse() based variable to capture input year month. Let's assume it's in the format yyyy-MM and it is called InputYM.

Create a same month last year variable (SMLY) as:

=FormatNumber(ToNumber(left([InputYM];4))-1;"0000")+"-01"

Your sales for same year last month are therefore

=Sum([Sales Amount]) Where ([Sales Year Month]=[SMLY])

Assuming that you don't have future sales (i.e. no sales dates greater than today) then your YTM variable would be:

Sum([Sales Amount]) Where (FormatDate([Sales Year Month];"yyyy")=left([InputYM];4))

I hope you can appreciate how tedious it will get to build these each time and consider moving them back to be universe objects.

Best of luck,

Mark

[Former Member]

Hi,

Firstly, is this on a BEx query? The trick here is that if you filter the report for the current month, then the same month last year is excluded from the query, so you can't do that.

Are you showing the month in the report? E.g. are your rows months, or some other characteristic? If you're not showing months in the report, and only want to compare current month with previous month, then you can do this with RKFs back in BEx.

But if you have a report where each row is a month, and one column is some measure for this year, and another column is that measure for the same month last year, then you'll need to do this in Webi. If you only want to show a fixed 12 months, then sort your report on month, not year/month and use a variable like sum([[revenue]] where (year(Current()) = year([[Calendar Day]])).

Or something similar.

Let us know what result you need, I'm sure the result you want is possible.