5 Replies Latest reply on Aug 29, 2013 1:42 PM by Shawn Wallwork

    Date Parameters / Calculated fields problem

    Siddharth Surana

      Hey Guys

       

      Getting stuck with Parameters and Calculated Fields

       

      I want to have a calender type (Like the one shown in Travel websites) date field filter. To achieve this, I had to create two parameters and 2 t/f Calculated fields to filter out information for the specified date range

       

      1. 2 Data Parameters created - Start Date & End Date

      2. Calculated field - After Start - Start Date <= Order Date

      3. Calculated Field - Before End - End Date >=Order Date

       

      Place these calculated fields on the filter shelf and select TRUE.

       

      This approach works well if the data source in which I created the Parameters / Calculated field remains the primary (In this workbook, the superstore Sales). But when I make the coffee sales as my primary data source, the Calculated filed do not filter out information for those date range as specified with parameters? (Unable to pull those calc fields on the sheet )

       

      Is there a workaround for this?

       

      I hope this question is clear. Please see the attached workbook

       

      Thanks

      -Sid

        • 1. Re: Date Parameters / Calculated fields problem
          Shawn Wallwork

          Thanks for the packaged workbook. Question: Are you trying to blend the two data sources onto a single worksheet? Or are you just wanting the to use the Superstore calcs on the Coffee worksheet? If it's the second one, then no they won't go onto the filter shelf. You just need to recreate the two calcs using the Coffee date field. Then you could put both these sheets on a dashboard side-by-side and the parameters will drive both, keeping them in sync.

           

          If you're asking a blend question that's a lot more difficult because even if you set up the relationships, Tableau is going to convert the secondary (Coffee) date field from a dimension to an aggregated measure (so it will be a single value that can then be displayed). To do this T wraps your secondary date field in an ATTR() function, which isn't at all what you want. I'm not that familiar with data blending, so I don't have a workaround for you. I suspect some Custom SQL could also solve this, but again I'll have to leave that to others.

           

          Hope this gets you started in the right direction,

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Date Parameters / Calculated fields problem
            Siddharth Surana

            Shawn Wallwork Thanks for replying. And am sorry am getting back to you so late. So after reading your response, it is a difficult thing to do when we blend the data.

             

            But could you expand further on how I can do this method that you mentioned:

             

            " If it's the second one, then no they won't go onto the filter shelf. You just need to recreate the two calcs using the Coffee date field. Then you could put both these sheets on a dashboard side-by-side and the parameters will drive both, keeping them in sync."


            So lets say I want to filter Coffee Sales data on Superstore Sales date. How do you think i should approach this when I use pure data blending?


            Thanks for your reply

            -Sid

            • 3. Re: Re: Date Parameters / Calculated fields problem
              Shawn Wallwork

              Siddharth, sorry I lost track of this one. What I was talking about in that comment was that you can use the same parameters across data sources without having to do a blend. In the attached I used the same date parameters to filter both charts without blending them. Does this work for you?

               

              --Shawn

              • 4. Re: Date Parameters / Calculated fields problem
                Siddharth Surana

                Shawn Wallwork

                 

                Just saw your reply back. Thanks a lot. So you did answer my question, such that, to filter data from 2 different data sources based on date - create a parameter, then create calculated fields in both the data sources. I get this. But my main concern was, what if I have to use the same date field in the calculated fields in both data sources. So lets say I want to filter only on Superstore sales Order Date and use that as a filter on both data sources. (I do not want to include any date filed from the Coffee Chain data source for a calculated field)

                 

                Is this possible?

                 

                Thnaks

                 

                -Sid

                • 5. Re: Date Parameters / Calculated fields problem
                  Shawn Wallwork

                  Siddharth, not sure what you're really after here, but somehow some way you'll either need to blend on a date or you'll have to use independent date fields from each data source and drive them using a parameter, or if you want to get crazy you can build a date range out of thin air using Joe Mako's scaffolding trick, which I wrote up somewhere that I can't find.

                   

                  --Shawn