4 Replies Latest reply on Apr 5, 2016 6:17 AM by Andrew Watson

    Calculated Measures based on certain dimension values

    Sharath BA

      Hi,

       

      I have some calculated key figures where I need to calculate for previous 3months / 6months based on the user selection on the "previous month". For example, if the selection is 201603 (previous month stored as int in this format) I want sum(sales) for 201603, 201602 & 201601. Again I use the calculated measure sum(sales) of 201603 for another calculation. Along with this I also need to calculate profit only for 201603.

       

      Is there a way to do this?

       

      Thanks!

        • 1. Re: Calculated Measures based on certain dimension values
          Andrew Watson

          This is possible. First you would need to convert the selected month to a date. You could try MAKEDATE for this or try other date functions. Then you would use a DATEADD to minus 3 months from this date to get your starting point of 'last 3 months'.

           

          Once you have the done the dateadd, create another calculated field to return sales for all dates greater than or equal to that date, in your example something like the following would do the job: IF  [Date] >= #2016-01-01# THEN [Sales] END

          • 2. Re: Calculated Measures based on certain dimension values
            Sharath BA

            Hi Andrew,

             

            Appreciate you response on this.

             

            Firstly, I cannot convert the date as the output should in this format only (this output is used as input to another upstream app). Moreover, the database is also not supporting if I convert to the date. I get message like invalid date and unable to get the catalog string.

             

            Is there a way that we can achieve, without changing the date format.

             

            I tried using LOD expression, using parameter but couldn't get the result.

             

            Please advice.

             

            Thanks!

            • 3. Re: Calculated Measures based on certain dimension values
              Sankarmagesh Rajan

              hi Sharath,

               

              We can create calc and parameter for this and get filtered previous data.

               

              IF datediff('month',[Date1],[Order Date])>=-2 and datediff('month',[Date1],[Order Date])<=0

              THEN "show" else "hide" end

               

              [Date1] is the parameter. Once the month selected then this will show previous two months.

               

              Thanks

              sankar

              • 4. Re: Calculated Measures based on certain dimension values
                Andrew Watson

                Without converting to a date how would you calculate the past 3 months? For example if you minus 3 from 201601 you don't get 201510. In Tableau try this formula to convert your data to a date from which you can minus months:

                 

                DATEPARSE('YYYYMMdd',str([Date]) + '01')

                 

                That adds 01 to your YearMonth to give 20160301 and then converts that to a date.

                 

                Note I'm not suggesting changing the format for display, only for calculation, so your upstream inputs shouldn't be affected.