3 Replies Latest reply on Nov 11, 2013 1:04 AM by . Indumon

    Calculated field for "Yesterday", "This week" and "this Month" on DateID

    san tos

      Hello

       

      I am new to tableau and have to create a report on the values based on "Yesterday", "this week" and "this month" dates... the date field's are Date ID and Month ID. from the cube and there is no copy or duplicate options available

       

      Someone please help with the calculated field.

       

      regards

      san

        • 1. Re: Calculated field for "Yesterday", "This week" and "this Month" on DateID
          . Indumon

          Hi Santos,

          I guess you are using the Microsoft Analysis Services Cube. If so you can achieve this by creating calculated members.

          You can refer the below link for more details.

          http://downloads.tableausoftware.com/quickstart/feature-guides/calcs.pdf

          • 2. Re: Calculated field for "Yesterday", "This week" and "this Month" on DateID
            san tos

            Hi Indumon,

             

            Thanks for your reply

             

            You are absolutely right. I tried with Calculated field but the date in the cube by default is a String and i changed the datatype to 'Date' in tableau and used the formula

            "IF     DATEDIFF('day',[created], today())  <  30 "

            But it throws an error because i directly cannot create calculated field rather right clicked on the field to create parameter and then from parameter created calculated field with the above formula.

             

            The error is "the parameter should be "date" cannot create calculated field with the string vdatatype"..

            • 3. Re: Re: Calculated field for "Yesterday", "This week" and "this Month" on DateID
              . Indumon

              Thanks for the detailed info.

               

              If I understood your requirement correctly, then you can achieve the goal using Calculated Member with MDX scripts.

              Please check the below required MDX formulas for each measure. ( Edit it with your measure and dimension names)


              Yesterday :

              SUM(Strtomember("[Time].[Date].&["+ format(now()-1,"yyyy-MM-ddT00:00:00")+"]"),[Measures].[Measure Name])

               

              This Month- MTD

               

              SUM(MTD(Strtomember("[Time].[Period].[Date].&["+ format(now()-1,"yyyy-MM-ddT00:00:00")+"]")),[Measures].[Measure Name])

               

              Week to Date: WTD

               

              SUM(WTD(Strtomember("[Time].[Period].[Date].&["+ format(now()-1,"yyyy-MM-ddT00:00:00")+"]")),[Measures].[Measure Name])

              // hierarchy must contain the Week .

               

              General : -30 days

               

              SUM({(Strtomember("[Time].[Date].&["+ format(now(),"yyyy-MM-ddT00:00:00")+"]")):

                (Strtomember("[Time].[Date].&["+ format(now()-30,"yyyy-MM-ddT00:00:00")+"]"))},

                [Measures].[Measure Name])

               

              Notes:

              My Time Dimension Name is Time // Replace with your Time dimension name

              [Period] : Hierarchy in the Time Dimension // Replace with your Hierarchy name

              Date Field : Date //Replace with your date field name

              Measure Name //Replace with your Measure name

               

              Create Calculated Member:

               

              Screen.jpg

               

              Result:

              Screen.jpg

               

              Hope this will help you!

              1 of 1 people found this helpful