date and measures 7 days ago

building reports webintelligence
1

hi,
i’m trying to create table with single row and in it i want to have date 7 days ago and measures just for that single day (for example [attemtps #]). I have created an object =RelativeDate(CurrentDate();-7). But when I added [attempts #] object i get wrong result. It contained sum of attempts for all dates together and i want to have it only for day 7 days ago.
any ideas guys?

i want to have it like this:

[date 7 ago] | [attemtps #] <-------
2008-08-15 | 456

for

[date] | [attempts #]
2008-08-22 | 146
2008-08-21 | 412
2008-08-20 | 332
2008-08-19 | 200
2008-08-18 | 412
2008-08-17 | 445
2008-08-16 | 302
2008-08-15 | 456 <--------

martinkemp (BOB member since 2007-10-03)

2

I think you’ll have to use a context - something like this:

=[Attempts #] where([Date] = RelativeDate(CurrentDate();-7))

David.Mitchell :uk: (BOB member since 2008-04-28)

3

Why don’t you create an object which do the same in the universe? (Just a suggestion)

Jansi :india: (BOB member since 2008-05-12)

4

Thx for reply guys.
David I’ve tried your method before but it shows empty cell and don’t why. So in this case Jansi I think your suggestion will give same result. I tried different way to get what I want but still no success. Anybody had same problem?

martinkemp (BOB member since 2007-10-03)

5

Look up context for reporting. You need to specify the level of aggrgation on the report. SUM([# Attempts];[Date]-7);[Date] type of thing. (That is not the exact syntax but understanding report level contexts will help you get your result)

mcliffordgoo :us: (BOB member since 2003-02-13)

6

I recommend you to create a new dimension like [Is 7 Days ago] defined as if [date]=relativedate([date];-7) then “T” else “F”
and then use [7 days ago Attempts #] = [Attempts #] where( [Is 7 Days ago] = “T”)
In order to test it, use [Date] and [Is 7 Days ago] together in a simple report to check the formula for [Is 7 Days ago] is working…
If it’s working you can use the new measure [7 days ago Attempts #].

Erkut

ERE (BOB member since 2008-06-04)