BusinessObjects Board

Retrieving QTD; MTD; WTD

building reports webintelligence
#1

Confused on how the data is being used and trying to come up with the Qty.
Hoping the gurus here can shed some light to this lightheaded Di

My data looks like this:

Destination…Station…YTD…QTD…MTD…WTD…SOLD
USA…FL…2016…2…6…22…1
USA…NY…2016…2…4…17…14
USA…NY…2016…2…4…16…4

The Sold needs to be summed by Quarter To Date; Month To Date, Week To Date and Year To date.

Data
Quarter has both 1 and 2
Month has 1; 4; 5; 6
Week has 2; 16, 17, 18,25

The Year I think is simple but trying to figure out how to ONLY use the SOLD for each other bucket is the trick here.
Not to forget that my Fiscal To Date buckets are not January thru December.

I tried searching but don’t see anyone having similar issue so trying to get it by :hb: without losing a hair :slight_smile:

di652 (BOB member since 2005-02-24)

#2

Okay, so talking out loud…

I have a calendar date where it ends on the last fiscal date. I think this helps
but now trying to use this date against week, month and quarter to date buckets.

I am so :? but talking helps :slight_smile:

di652 (BOB member since 2005-02-24)

#3

Searching also helps

Nick Daniels :uk: (BOB member since 2002-08-15)

#4

Thanks tried this and the UNIVERSE(IDT) keeps failing

SUM(CASE WHEN dateadd(MONTH, datediff(MONTH, 0, FiscalDate), 0)) = dateadd(MONTH, 0, datediff(MM, 0,curDate()), 0) Then QtySold) else 0 END)

Says it doesn’t know what MONTH is? I also tried MM…

I really really really hate IDT. :reallymad:

di652 (BOB member since 2005-02-24)

#5

I tried putting quotes around and changed it to ‘MM’ now I get…

DateDiff is Unknown!!!??

di652 (BOB member since 2005-02-24)