1 Reply Latest reply on Jun 18, 2018 8:48 AM by Alex Braun

    Calculation of Table between previous months and years

    Danilo Carvalho

      Everyone, good afternoon!

      I gave a search and could not find a forum with this doubt, I apologize if it already has.

       

      I need help calculating the tableau.

      I need to return in a table the value of the current month, value of the previous month and value of the month of the previous year, being that in the filter I can select only 1 year.

       

      I'm attaching the workbook and an excel print of how I'd like it to look.

      Can someone help me.

       

      Thank you

       

      EndView.png

        • 1. Re: Calculation of Table between previous months and years
          Alex Braun

          Depending on weather you want the output to be automatic or manual, this can be done pretty easily.

           

          First, you will create 2 parameters.


          The first, I will call Date Type. Here you can set some pre-defined periods. So I will put 3 options.

           

          Current Month

          Previous Month

          Custom

           

          The second parameter will be a date parameter, you can name it whatever you would like.

           

          Next you will need to create a calculated field to give you the reference date, the calculation is as follows [Reference Date]

           

          if [Date Type] = 'Current Month' then datetrunc('month',Today())

          elseif [Date Type] = 'Previous Month' then dateadd('month',-1,datetrunc('month',Today()))

          else datetrunc('month',[Date Parameter])

          end

           

          The next calculated field will be for our filter. [Date Filter]

          if [Reference Date] = datetrunc('month',[Data Date]) then 'T'

          elseif dateadd('month',-1,[Reference Date]) = datetrunc('month',[Data Date]) then 'T'

          elseif dateadd('year',-1,[Reference Date]) = datetrunc('month',[Data Date]) then 'T'

          else 'F'

          end

           

          Then apply the [Date Filter] and accept 'T' to get these.  You can also create 3 separate calculated fields that instead of 'T', you put the period name for your month,month-1 and month previous year headings.

           

          I have added a file with the calcs and applied the filter.