# contract in the last 12 months

building semantic layer
1

Hello Friends I am new to EDW Universe i need to know how to make the measure # contract in the last 12 months . i have a measure in table # of contract and time field calmonth so in where clause what is that code that i can write

pls help me

Thanks

Kind Regards

soniya

soniyakaren (BOB member since 2009-07-21)

2

Hi soniya,

Can you please explain in detail? I think you need to make measure object with some condition?

Thanks,
Arun

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

3

By # do you mean sum? So you want the sum of the values in the CONTRACT column in the past 12 months?

You don’t say what database you are using. In Oracle I’d have a measure that says:


SELECT SUM(CONTRACT)
WHERE CALMONTH > ADD_MONTHS(SYSDATE,-12)

debbie

Debbie :uk: (BOB member since 2005-03-01)

4

If you use the where clause then that’s going to be included in all the data providers you use that object in, which may not be what you intend.

you’re probably better with something like:


SUM(CASE WHEN MY_DATE > ADD_MONTHS(TRUNC(SYSDATE),-12) THEN CONTRACT ELSE NULL END)

slimdave :uk: (BOB member since 2002-09-10)

5

Or better still, just SUM(CONTRACT). And add the restriction in via a filter if/when required.

I tend to avoid case statements in measures as the query will fetch all the records before it evaluates the statement, which in my case considerably increases performance time. Depends on the data set I guess.

debbie

Debbie :uk: (BOB member since 2005-03-01)

6

Hello Friends, Thanks so much for help. let me clarify the requirement pls help with final solution ,

I need to add all contracts in 12 months

so like in dec we have 30 contract in nov 27 so will add all and say like 57 contract if remaing month contract is zero

so now all i have is a field contract # in table admin.contr, please let me know the steps

soniya

soniyakaren (BOB member since 2009-07-21)

7

So you would want an object that dynamically counts your contracts based on either the current date or a user defined report date. Which means that you will have to use a prompt, calculate the period based on that input and count the number of contracts within that period.

I don’t think anyone is going to give you a step-by-step instructions to do this. We are all pretty busy ourselves. But based on this post and your other posts, perhaps you should request a few Business Objects courses from your employer?

ErikR :new_zealand: (BOB member since 2007-01-10)

8

Hello Erik, I already know how to work this as debbie pointed i was looking for some help to make it work, if u do not want to help please ignore it and do not reply, I already worked many years on this

soniyakaren (BOB member since 2009-07-21)