Current Month, Last Month and Last Year Current Month

[sandeepsinha327]

Hi All,

I have a requirement in a webi report which should display the Net price of certain materials for the Month Selected in prompt where the field used us Calendar Day as To and From.

Also, in the report output there should be a comparison of three date fields which are Selected Month (i.e. based on the input in prompts) and then Previous month of the same year and same month of last year.

For Example

     Material Name     Oct-2015     Sep-2015     Oct-2014

          ABC                    235          123               12

          DEF                    454          65                654

          WRE                  65423     6423               9876

          WDS                  78452      9687               313

So here we have Selected Month i.e. Oct 2015 where the prompt used is 01/10/2015 to 31/10/2015.

Please help me out here. Also let me know if any more information is required.

Regards

Sandeep

[Former Member · Accepted solution]

Hello Sandeep,

Please create formula as mentioned below;

1 [Lastmonth]=FormatNumber(If MonthNumberOfYear([BDate])=12 Then 1 Else MonthNumberOfYear([BDate])-1;"#")

2 [LastYear]=FormatNumber(If MonthNumberOfYear([BDate])=12 Then Year([BDate])-1 Else Year([BDate]);"####")

3 [LastMonthDate]=Month(ToDate(("01/"+[Lastmonth]+"/"+[LastYear]);"dd/MM/yyyy"))+" "+[LastYear]

So the LastMonthDate will give you Last month for current Year.

Now to get the data for Same month Previous Year

[LastYearDate]=Month([BDate])+" "+FormatNumber(Year([BDate])-1;"####")

I would also suggest that Since you have created prompt using 01/10/2015 to 31/10/2015. so data will be available for 2015 only and you are expecting data for 2014 so probably you have to adjust the prompt accordingly to get the data.

Regards

Niraj

[dinesh15787 · Accepted solution]

Hi Niraj,

       Your syntax to generate the Last Month & other two looks fine. But I don't think your solution will work. Because data is restricted for Oct 2015. So how do you expect to see the data for Last Month if it is not brought into the report. He should bring the previous year data somehow in to the report ie,. Data from 01/10/2014 to 01/10/2015.

Also he has mentioned that date selection is from the Calendar and not from the Lov's. The deciding factor is the user input to/from date.

So prompt has to be captured and converted to date as below, may be in a variable 'XXXX'

FormatDate(ToDate(UserResponse("Calendar as to/from";"INPUT_DATE_TIME"); "dd/MM/yyyy").

Then your steps will be applied along with if else to merge the Above date with the Object holding the date[Bdate].

Correct me if i am wrong!

Regards,

Dinesh

[Former Member · Accepted solution]

Hello Dinesh,

Appreciate your feedback but please notice last paragraph of my reply on the initial post I have highlighted that part.

Regards

Niraj

[dinesh15787 · Accepted solution]

Sorry, yes you do mentioned it. I missed to read it.

[Former Member · Accepted solution]

No worries mate cheers..

[sandeepsinha327 · Accepted solution]

Hi Niraj,

Thank you for your reply. It was really helpful.

This report being a cross tab , I had to put Net Price (Measure) in the report body but the problem is when I run it for one year also as suggested by you it fetches the same value in all the columns.

Am I missing something here. Please let me know.

Each Column body should have net price values for the respective years or months.

Regards

Sandeep

[Former Member · Accepted solution]

Hello Sandeep,

So you must be having Some date Dimension based on which you are calculating the Net Price(Lets Call that TransDate as of now) So you need to restrict the data based on the dates calculated above

So for Current Month your NetPrice Measure will be something like

[CurrentMonthNetPrice]= Sum([NetPrice] Where ([TransDate]<=[CurrentMonthdate]))

[LastMonthNetPrice]=Sum([NetPrice] Where ([TransDate]<=[LastMonthdate]))

[LastYearNetPrice]=Sum([NetPrice] Where ([TransDate]<=[LastYeardate]))

and then finally instead of bringing in directly NetPrice you need to pull these variables.

Hope I was able to explain

Regards

Niraj