cancel
Showing results for 
Search instead for 
Did you mean: 

Sales comparision of today and last year today on Vertical table

erdem_sephanelioglu
Participant
0 Kudos

Hi everybody,

I have such scenario that client wants to see sales values of two columns. Left shows sales of current date and the right will show the last year today .

How can I construct variables here lets say the last day today as dimension and body part related sales of last year today.

The display would be like

Brand          01.08.2013          01.08.2012

Regards.

Eddy.

Accepted Solutions (1)

Accepted Solutions (1)

erdem_sephanelioglu
Participant
0 Kudos

I have implement two dimension type variables showing today and last year today.

However the issue is displaying sales values for two particular dates

When I construct vertical table such as

Column1    Column2       Column3

Products     Sales_CY     Sales_LY

where

sales_CY=[Bex].[Sales] Where([Calendar .day]=[Yesterday])

Sales_LY=[Bex].[Sales] Where ([Takvim yılı]=[LYyesterday])

helping variables

Yesterday=ToDate(RelativeDate(CurrentDate();-1);"dd.MM.yyyy")

LYyesterday=ToDate([_Day]+"."+[_Month]+"."+[_Year(-1)];"dd.MM.yyyy")

Year(-1)=FormatNumber(Year(CurrentDate())-1;"#")+""

_Day=DayNumberOfMonth(RelativeDate(CurrentDate();-1))+""

_Month=MonthNumberOfYear(CurrentDate())+""

_Year(-1)=FormatNumber(Year(CurrentDate())-1;"#")+""

The problem is sales_CY and Sales_LY shows the annual value total sales of relevant product at rows not for the relevant day.

how can ı relate the measures with where condition

Thanks.

Regards.

Eddy.

Former Member
0 Kudos

Hi Eddy,

I think the formula to be used in measure is correct.

i.e. Sales_LY = BEx. Sales where Sales_Date = LY_Date.

But , issue is the way you are calculating the LY_Date (last year date). I am not sure if using the mentioned formula you will get the correct last year date corresponding to current sales date.

Thanks,

Rohit Garg

erdem_sephanelioglu
Participant
0 Kudos

Rohit,

Ly date is the one you have suggested and I applied it under a dimension variable. I draagged it on the canvas it shows 31.07.2012 which is correct.

Added image is the current version of the vertical table results related to both tıme columns. It is very very weird to me as without using where condition and with the same dimension the cells are all filled with values.

E.

Answers (3)

Answers (3)

Former Member
0 Kudos

Hello Eddy,

There are many ways of getting this done, but per my understanding the below solution could be optimal one as it will automatically take care of leap years unlike other solutions.

You can create a variable with this formula.

=ToDate(Concatenation(DayNumberOfMonth(<date_Object>),"/",MonthNumberOfYear(<Date_Object>),"/",(year(<Date_Format>)-1)),"dd/MM/yyyy")

*<Date_Object> - current date

* dd/MM/yyyy - should have MM in upper case, as WebI considers "mm" as minutes instead of month.

Let us know if it works for you.

Thanks,

Rohit Garg

Former Member
0 Kudos

Hello Eddy,

check also the relativeValue function:

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp5_ffc_en.pdf#page=202

Best regards,

Victor

Former Member
0 Kudos

Hi eddy,

You can use the below for deriving current date and same date on previous year.and derive your metric columns as below

Current day= CurrentDate()

same date on previous year=If(FormatDate(RelativeDate(CurrentDate();-365);"DD") = FormatDate(CurrentDate();"DD");RelativeDate(CurrentDate();-365); RelativeDate(CurrentDate();-366)

Ensure that dates are in same format in the where clause.

sales of current date = [Sales] Where ([Reported/submitted date]=[Current day])

sales of last year today=[Sales] Where([Reported/submitted date]=[same date on previous year])

Thanks

Mallik

erdem_sephanelioglu
Participant
0 Kudos

Hi Mallikarjuna,

Thank you for the prompt response however going 365 days back would give improper date for example when the date is 29.02.yyyy so better to create exact date of last year.What would be the best approach?

regardıng where condıtıon I guess I have an issue to determine the right form of the right side of the conditon)

How can I make sure [Reported/submitted date] and [same date on previous year] are at the same format

Is it enough to make sure each has identical display when drag them on the WEBI canvas?

Eddy.

Former Member
0 Kudos

Sorry for the delay in responding. Good to know that your problem is solved.

Seems like in your case Sales value is stored as Summarized and stored by As of Date, if so then you get the right sales value across the date, so if you place previous year date/Current date along with sales it gives the sales value across that date.

Thanks

Mallik