6 Replies Latest reply on Nov 12, 2015 5:33 AM by pooja.gandhi

# Values with different dates for calculation

Hi there,

First of all I try to describe what I am going to achieve:

I have got a database with many fields but I am interested in "bookingref" and "startdate". "startdate" field contains 2006-2016 date range  and for each date there is certain value of bookingref.

What I am trying to achieve is to get within one Sheet next 7 days start from today's date and for each one of them bookingref value for that date, bookingref value for the same date but last year and third field with some calculations.

Something like:

 Date Booking Ref 2015 (A) Booking Ref 2014 (B) Calculation on (A and B) 09/11/2015 200 100 B/A*0.20 10/11/2015 352 200 B/A*0.20 11/11/2015 152 354 B/A*0.20 12/11/2015 123 437 B/A*0.20 13/11/2015 700 502 B/A*0.20 14/11/2015 500 132 B/A*0.20 15/11/2015 126 245 B/A*0.20

I use either cube data source or sql database and I have been trying different solutions so far but none of them meets my expectations.

The best one I came up with was with using LOOKUP(ZN(COUNTD([Bookingref])), -365) as calculated field but I can not display only 7 days worth of data starting from today's date onward.

Using cube as data source is different because LOOKUP(ZN(WINDOW_COUNT([Booking Count])), -365) doesn't show any data.

I have ran out of ideas so any help would be much appreciated.

Thanks Guys

• ###### 1. Re: Values with different dates for calculation

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

Time periods in Tableau

If I have time, I'll come back to you

Cheers

Mark

1 of 1 people found this helpful
• ###### 2. Re: Values with different dates for calculation

Hi Mark,

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,

• ###### 3. Re: Values with different dates for calculation

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

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

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

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.