2 Replies Latest reply on Mar 16, 2017 6:34 AM by Eleonor Hellblom

    MAT calculated fields

    Dimitar Stoimchev

      Hi,

       

      I'm trying to created a calculated fields which contain my data grouped by MAT. I get dataset once quarterly and I have 20 quarters rolling. Now I'm using this syntax:

       

      if

      [TimePeriod]="20151001" or [TimePeriod]="20150701" or [TimePeriod]="20150401" or [TimePeriod]="20150101"

      then [Sales]

      else 0

      end

       

      It cuts only sales data for last 4 quarters (it's string field called 'TimePeriod'). My idea is somehow to put in this syntax index for TimePeriod and when I get Q1 '16 data, instead of replacing [TimePeriod]="20150101" with [TimePeriod]="20160101" manually, to have it automated. It will be really useful for me, because I can do this for all MATs. Something like:

       

      LAST MAT:

      if

      [TimePeriod]="n" or [TimePeriod]="n-1" or [TimePeriod]="n-2" or [TimePeriod]="n-3"

      then [Sales]

      else 0

      end

       

      PREVIOUS MAT:

      if

      [TimePeriod]="n-4" or [TimePeriod]="n-5" or [TimePeriod]="n-6" or [TimePeriod]="n-7"

      then [Sales]

      else 0

      end

       

      .....

       

      Thanks in advance!

       

        • 1. Re: MAT calculated fields
          Jonathan Drummey

          Hi,

           

          Rather than doing this through string manipulation my suggestion is to use dates, for example creating a newTimePeriod field using:

           

          DATEPARSE("YYYYMMdd", [TimePeriod])

           

          If you have a LatestDate column in your data source then you can use that, otherwise you can build one using a FIXED Level of Detail expression:

           

          {FIXED : MAX([TimePeriod])}

           

          The column in the data source is preferable as it will be faster in Tableau.

           

          Then there are a number of ways you can get LAST MAT and PREVIOUS MAT Sales, here's a way that works like what you described.

           

          LAST MAT:

          IF [newTimePeriod] >= DATEADD('year',-1,[LatestDate]) THEN [Sales] END

           

          PREVIOUS MAT:

          IF [newTimePeriod] >= DATEADD('year',-2,[LatestDate]) AND [newTimePeriod] < DATEADD('year',-1,[LatestDate]) THEN [Sales] END

           

          Jonathan

          2 of 2 people found this helpful
          • 2. Re: MAT calculated fields
            Eleonor Hellblom

            Hi Jonathan,

             

            Your answer helped me massively, however I am having some issues when I need to do a MAT on another calculated field, as there is then a conflict with aggregation and I haven't fully got my head around that.

             

            I have the following calculation with a FIXED LOD:

            (sum([Sales])

            /

            sum({ FIXED [Country], [Category], [Order Date] : sum([Sales])})

             

            When putting that in the calculation you provided above I get this:

             

            If you or anyone else could help me getting this calculation correct it would be greatly appreciated.

             

            Thank you in advance!