7 Replies Latest reply on Sep 12, 2017 7:13 AM by Vincent Lange

    Multiple Dimensions and Granularities next to each other

    Vincent Lange

      Hey Tableau-community

       

      I have searched the forum quite a bit for a solution to this, but I still couldn't quite figure it out; I am familiar that my desired outcome might be achievable by creating multiple tables and combining them on the dashboard to look like one, however I think it might be possible to do in one worksheet.

       

      I want to have a table where the columns look like so:

      As you can see there are several different date-dimension (day, week, month, year), that are also relative, and also percentage-changes, and I just can't figure out how to achieve the results, or at least something comparable.

      I am working with a live data-source btw.

       

      I really hope you can help me, and if not, I still wish you a pleasant day


      Cheers,

        • 1. Re: Multiple Dimensions and Granularities next to each other
          Tushar  More

          Hi Vincent,

           

          Please check the below mentioned thread. This will give an idea about different time calculations.

          Time periods in Tableau

           

          If you face any problem let me know. And, please attached a sample workbook.

           

          ~Tushar

          1 of 1 people found this helpful
          • 2. Re: Multiple Dimensions and Granularities next to each other
            Vincent Lange

            Hey Tushar,

             

            thanks for your quick reply and the link you provided.

            However the workbook does not provide the solution for my problem:

             

            In the workbook you linked, they created custom date-measures that show different time-periods for one measure only in one table("Random Value" in this case).

            What I am looking for, are custom-dimensions, that apply different time-periods on multiple measures in one table(just as in the screenshot I provided).

            • 3. Re: Multiple Dimensions and Granularities next to each other
              Tushar  More

              Hi Vincent,

               

              I have created some calculations based on your image in attached workbook created in v10.3.

              And, the final output looks like this

              Hope this gives you an idea and you can take this to the next level from here.

               

              Let me know if it helps.

               

              ~Tushar

              1 of 1 people found this helpful
              • 4. Re: Multiple Dimensions and Granularities next to each other
                Vincent Lange

                This is awesome Tushar, thanks a bunch!

                The custom dimension "Time period" was exactly what I was looking for:

                 

                IF DATEDIFF('day',[Order Date],TODAY())=0 THEN "Today"

                ELSEIF [Order Date]<=TODAY() and DATEDIFF('year',[Order Date],TODAY())=0

                THEN "YTD"

                ELSEIF DATEDIFF('month',[Order Date],TODAY())=-1 THEN "Last Month"

                ELSEIF DATEDIFF('week',[Order Date],TODAY())=-1 THEN "Last Week"

                ELSEif

                DATEDIFF('week',[Order Date],TODAY())=-12 THEN "Week 12"

                END

                 

                Thanks a lot and have a pleasant day

                • 5. Re: Multiple Dimensions and Granularities next to each other
                  Vincent Lange

                  Hey Tushar, quick update:

                   

                  In the workbook, the dates were mistakenly calculated with DATEDIFF (...)=-1, which gives you the NEXT date, instead of the LAST one (unless I understood something wrong here?).

                  This leads to an annoying bug:

                   

                  If you do the "Time Period" calculation with DATEDIFF(...)=1 instead, it "filters" out the periods of the first IF-statements out of the calculations for the latter ones.

                  i.e.: If you calculate today, last month and last week in your IF and ELSEIF-statements first, and then afterwards YTD, then the data for today, last week and last month do not sum into the YTD, because they have been "filtered out" before.

                  if you do it the other way around and first do the IF statement for YTD, then the other fields do not even show up, since their ranges have been "filtered" out.

                   

                  There might be a "dirty" workaround for this, which is creating multiple (duplicate) instances of [Order Date], to use in each IF-statement, but it is not quite the elegant solution I was looking for, I am afraid haha

                   

                  I would be interested what your 2 cents are about this, so if you can find the time, let me know

                  • 6. Re: Multiple Dimensions and Granularities next to each other
                    Tushar  More

                    Hey Vincent,

                     

                    You are right. Sorry for that. I've updated the calculation in the attached workbook. I've tested it and hope getting correct result except for YTD.

                     

                    I'll let you know if I fix this.

                     

                    ~Tushar

                    1 of 1 people found this helpful
                    • 7. Re: Multiple Dimensions and Granularities next to each other
                      Vincent Lange

                      The problem remains, even in this version with the "Order Date Parameter":

                       

                      Everytime there is an overlap in dates, the data gets "filtered" out of the date-range in the next IF-statement.

                       

                      (you can't notice the probleme with the last week- and last month-values in this workbook, because they reference different months (September for last week, and August for last month), but if you change last week to last week -3, which is a week in August, you can see the problem I am talking about).

                       

                      I tried to do the workaround, using different duplicates of [Order Date], but I couldn't get it to work.

                       

                      I think the issue is also well described in this thread:

                      Multiple/overlapping values in a case statement?

                       

                      However the cross-join solution doesn't quite work for me, since I don't have access to it, so I think I might just have to put multiple tables next to each other or find a workaround with lookup-calculations.