BusinessObjects Board

MTD YTD Measures

building semantic layer
#1

I have to design a new report as follows: It runs daily, monthly and yearly.

Group1:

Item 1 MTDSalesRevenue YTDSalesRevenue PreviousYearSales
Item 2 MTDSalesRevenue YTDSalesRevenue PreviousYearSales
…
…
Item N MTDSalesRevenue YTDSalesRevenue PreviousYearSales

              Sub-Total	                   Sub-Total                 Sub-Total

Group2:

             Sub-Total	                   Sub-Total                 Sub-Total
             
             Grand Total                 Grand Total              Grand Total

The report should display according to the user entered date range and SKU_FLAG

Web Intelligence user Prompts:

  1. Trans_Date ‘In Between’ Date1 & Date2
  2. SKU_FLAG in List

BOBJGuy :uk: (BOB member since 2010-02-25)

YTD CONDITION OBJECT
SQL code
#2

Anybody there to help??? :hb: :frowning:

BOBJGuy :uk: (BOB member since 2010-02-25)

#3

Hi,
You posted both within 2 hours. So please wait until one full business day is completed.

To get the MTD object, you can make use of the add_months & last_day oracle functions to get.

<date object> between (select last_dayadd_months(sysdate,-1))+1 from dual) and (select last_day(sysdate) from dual)

To get the YTD obejct.
Again make use of sysdate to tally till the today and for the current year.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

#4

How can I change my SQL to create the Object? I guess I need to create a measure object that calculate the sales revenue MTD and YTD, not a date object.

BOBJGuy :uk: (BOB member since 2010-02-25)

#5

Please follow this rule about post bumping:
https://bobj-board.org/tos#heading--bumping

All rules and guidelines of this forum are here:
https://bobj-board.org/tos

Thanks.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#6

Sorry Marek. I am new to this forum and I was looking for a quick solution.

BOBJGuy :uk: (BOB member since 2010-02-25)

#7

Try this one. I think this should work for you.

MTD
SUM(CASE WHEN TRUNC(Trans_Date ) BETWEEN TRUNC(SYSDATE-1,‘MM’) AND TRUNC(SYSDATE) THEN SalesRevenue ELSE NULL END)

YTD
SUM(CASE WHEN TRUNC(Trans_Date ) BETWEEN TRUNC(SYSDATE-1,‘YYYY’) AND TRUNC(SYSDATE) THEN SalesRevenue ELSE NULL END)

Thanks
Arun

arung :india: (BOB member since 2007-07-18)

#8

I tried the samething. But I am getting wrong results. Do I need to use any @prompts here? I am getting same results for MTD and YTD and also they are not correct results.

BOBJGuy :uk: (BOB member since 2010-02-25)

#9

If you don’t use a prompt, then how will the user provide the date for the report?

What sort of where clause do you have? If you are getting the same results for MTD that you get for YTD then I suspect that you have the wrong where clause on your query.

It would help if you posted an example of your SQL statement. The entire statement, select, where, and so on.

Dave Rathbun :us: (BOB member since 2002-06-06)

#10

I am using two date prompts at Web Intelligence level, not at the Universe level. Start Date and End Date.

Unfortunately I can’t provide the exact SQL due to security concerns. Brits are very much concerned about the Data protection act

BOBJGuy :uk: (BOB member since 2010-02-25)

#11

SQL is code, not data. The reason I asked to see the code is when you put MTD and YTD and other potential objects together, they need to work with each other, not against. You said that you got the same result for MTD that you did for YTD, which tells me they’re not working together properly.

If you’re not going to show code or provide a more detailed explanation as to what you’re doing, it’s going to be hard to help. :slight_smile:

If you want to base your MTD and YTD on a start / end date from the user, then you have to reference the prompt in the object in the universe.

Dave Rathbun :us: (BOB member since 2002-06-06)

#12

Hi

BOBJGuy :uk: (BOB member since 2010-02-25)

#13

Dave,

I read your post on Measure and Aggregate projection. It was great!

BOBJGuy :uk: (BOB member since 2010-02-25)