BusinessObjects Board

Prior Month Quantity Sold Calculation

building semantic layer
#1

Could somebody tell me why this logic is not working? I am trying to calculate quantity sold for previous month (do not have columns for PrevMoBegDate and PrevMoEndDate) but get zero for value.

sum(case when dbo.SalesDetail.DateSold between (dateadd(m,DATEDIFF(m,0, CYPriorMonth.BegMo)-1, 0)) and (dateadd(m,DATEDIFF(m,-1, CYPriorMonth.EndMo)-1, -1)) then dbo.SalesDetail.QuantitySold else 0 end)

When I use getdate() instead of CYPriorMonth.BegMO and CYPriorEndMO, the logic works fine and I get a value.

Thanks in advance!

samistl (BOB member since 2012-12-26)

#2

Have a look in this topic for the getdate() equivalents for start of previous month and end of previous month :slight_smile:

#3

[Moderator Note: Moved from General Discussion to Semantic Layer / Universe Designer]

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