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

    Values with different dates for calculation

    Adam Gawrys

      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.

      1.JPG

       

      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:

          

      DateBooking Ref 2015 (A)Booking Ref 2014 (B)Calculation on (A and B)
      09/11/2015200100B/A*0.20
      10/11/2015352200B/A*0.20
      11/11/2015152354B/A*0.20
      12/11/2015123437B/A*0.20
      13/11/2015700502B/A*0.20
      14/11/2015500132B/A*0.20
      15/11/2015126245B/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.

       

      2.JPG

       

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

       

      Thanks Guys

        • 1. Re: Values with different dates for calculation
          Mark Fraser

          Hi 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

          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
            Adam Gawrys

            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:

             

               

            DateCalculation 1Calculation 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

              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

                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

                  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

                    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.