
1. Re: Values with different dates for calculation
Mark Fraser Nov 9, 2015 7:02 AM (in response to Adam Gawrys)1 of 1 people found this helpfulHi Adam
Here is a formula to get you next 7 days... using today function
IF [Date] > TODAY() AND [Date] < TODAY()+8 THEN 'NEXT 7 DAYS' ELSE NULL END
I would suggest you can use this methodology to leverage the rest of the items you need... I mean something like this...
IF [Date] > TODAY() AND [Date] < TODAY()+8 THEN [Bookings] ELSE NULL END
I wrote this sometime ago, maybe helpful
If I have time, I'll come back to you
Cheers
Mark

2. Re: Values with different dates for calculation
Adam Gawrys Nov 9, 2015 7:36 AM (in response to Mark Fraser)Hi Mark,
Thanks for quick reply.
Your answer sorts out one of my issues but I am still looking how to get last year value without using LOOKUP(ZN(COUNTD([Bookingref])), 365) for sql and
SUM
( { [StartDate].[Date].CurrentMember.Lag(365) }
, [Measures].[Booking count]
)
for cube. For both I need to have all dates in one sheet so formula can look 365 rows back to pick up a value. Ideally query would do the job if possible to use with calculation field something like:
Date Calculation 1 Calculation 2 09/11/2015 Calculation 1 = select count(bookingref) from abc where date = Date in 2015
Calculation 2 = select count(bookingref) from abc where date = Date in 2014
Thanks,
Adam

3. Re: Values with different dates for calculation
Mark Fraser Nov 11, 2015 2:42 AM (in response to Adam Gawrys)Hi Adam
Apologies for the delayed reply
You have the year function which can be used in logical tests
IF YEAR([Order Date])=2014 THEN 'Last Year' else 'Older' END
Extending that...You could also create a YEAR([DATE]) field and use then use that in an LOD
{FIXED [YEAR] : SUM([Sales])} < This will return for everyday, a single value for the year total.
You can see it here in superstore
It appears you can't do this
{FIXED (YEAR([DATE]) : SUM([Sales])}
(don't know why  it would save doing it in 2 steps!)
Have I understood the problem? Does the above make sense/ and is it suitable?
Cheers
Mark

4. Re: Values with different dates for calculation
pooja.gandhi Nov 11, 2015 5:29 AM (in response to Mark Fraser)Mark,
You can't use a calculation in the dimension declaration of an LOD expression and hence you can not use YEAR([Date]). To use just the year datepart, you can create a custom date field from the original date dimension and use that in the LOD expressions . So that fixes the year datepart and aggregates the measures to whatever you define it to be.
Pooja.

5. Re: Values with different dates for calculation
Mark Fraser Nov 12, 2015 1:51 AM (in response to pooja.gandhi)Hi Pooja
Thanks for your feedback  I'm aware that this isn't possible and so I published my answer including the 2 step workaround. What the question prompted me to think is  why not? Why can't I use the YEAR() function on the left?
(and also later  why doesn't it work with hierarchies)
So, I raised it with the ambassadors  LoD Question (sorry not everyone will have access )
and then it was suggested it would be interesting to open the discussion to everyone, hence Using calculations 'on the left'  LoD
Jonathan & Simon in particular had some really interesting thoughts and ideas...
Cheers
Mark

6. Re: Values with different dates for calculation
pooja.gandhi Nov 12, 2015 5:33 AM (in response to Mark Fraser)Hi Mark,
Aah! I just noticed the Ambassadors thread and the other. Sorry I misunderstood. Great discussion indeed. I know LOD expressions are pretty powerful in a lot of scenarios and I wish to utilize it more and more. Discussions like these can foster many ideas for improvements in the functionality.
Thanks,
Pooja.