5 Replies Latest reply on Sep 19, 2018 4:12 PM by Hari Ankem

    Calculation to return result from a specified date?

    Jane Birman

      I am having trouble finding a response to my specific issue, and am wondering if it's simply not done this way in Tableau.

       

      I have a workbook that will be updated monthly. There are roughly 200 data points that have a variety of attributes, but generally every month users will input a number every month and that number will be the result for that month for that KPI. It is sensitive information so I cannot upload an example, but I will make a rough version below.

      KPI ID
      UploaderCurrent Month's Result
      1.1Sarah30
      1.2Chelsea22
      1.3Cindy1
      1.4Laura.5

      So every month, the values will update to the most recent month. Each data entry has a period attached to it so we can keep track of which month it belongs to, and I managed to convert the period into a specific date. Although the current month's result is displayed as such, it also has the specific upload date attached to it both as a date (September 1 2018) and a period. Every data entry uploaded has the same parameters.

       

      My issue is that for the sake of analysis, I need to create certain calculations using previous values. So for example, in Current Month's Result, I have the results from September 2018. I want to obtain the results from August 2018 within a calculation. I do not want to use a table calculation because I want to obtain the entry so that I can use it in another calculation. I want to create a calculation that will return the results from a specific date. I feel like it should be something simple like a filter but I have not been able to get valid results. In other words, if I want to lookup the result that has been input during a specific month (whether it's the previous month or 5 months before the current month or something different), how can I write a calculation that would return those values?

        • 1. Re: Calculation to return result from a specified date?
          Hari Ankem

          If you cannot provide your sensitive data, please at least provide the data which is structured similarly to what you are stating. And using that data set, please provide what your expected output is.

          • 2. Re: Calculation to return result from a specified date?
            Jim Dehner

            Hi Jane

            difficult without seeing your twbx workbook so the response here is more concept than specific formulas - you will need to adapt

             

            first couple of comments on how tableau works

            filters - filter value out of the data table in the viz -the data are either in or out of the filter

            table calculations are done on the underlying table for the viz - there include the ways you move around the table to pick up previous values or aggregate table entries together

             

            That said there is an option available and it is dependent on the "period' that you load - you can use an LOD to fix the value associated with a specific period - LOD's create a separate layer of disaggregated data in your data and as so they can be further aggregated without using table calculations - you want to use Fixed (it will return a result that can be used as a measure or converted to a dimension - (not the same with Include or exclude) - fixed will form combinations of the dimensions that precede the colon and then perform the function(s) that follow the colon - those functions can include conditional if then type , numeric or min(),Max() type comparisons on numbers or strings - and they must be aggregated in the LOD - note as stated before - while they aggregate date they are not aggregates in themselves -

             

            now if you have a "period" that can be identified (i.e. a dimension) that relates directly to the LOD and can be used in a conditional statement -- e.g if [period] ="whatever' then - you can do summations, comparisons, differences tec on the LOD's

             

            Good luck

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Calculation to return result from a specified date?
              Jane Birman

              I created a mock data set to explain what I meant. Let's say I need to create a calculation that will compare all data points from May 2018 to the current month. When I try to filter by May 2018 to obtain only results from May, it refers to the current month's results. I do not want to do a table calculation because I will need to use the result from this calculation in future calculations.

               

              In other words, lets look at ID #1.2.5 in the example worksheet. I want to know the difference between the May 2018 results and the August 2018 results only. How do I point to just the value stored for IDs in May 2018? The answer for the row corresponding to ID # 1.2.5 should be -0.0016. Please let me know if you would like to me explain differently.

               

              My question is quite general; in the picture below I clicked on a month of results in my data set. How would I refer to that month within a calculation? Like, a generic calculation that would return results only from a specified month.

              • 4. Re: Calculation to return result from a specified date?
                Jane Birman

                I created a mock data set to explain what I meant. You can see it attached to the other reply in this post.

                 

                Let's say I need to create a calculation that will compare all data points from May 2018 to the current month. When I try to filter by May 2018 to obtain only results from May, it refers to the current month's results. I do not want to do a table calculation because I will need to use the result from this calculation in future calculations.

                 

                In other words, lets look at ID #1.2.5 in the example worksheet. I want to know the difference between the May 2018 results and the August 2018 results only. How do I point to just the value stored for IDs in May 2018? The answer for the row corresponding to ID # 1.2.5 should be -0.0016. Please let me know if you would like to me explain differently.

                 

                My question is quite general; in the picture below I clicked on a month of results in my data set. How would I refer to that month within a calculation? Like, a calculation that would return results only from October 2017.

                • 5. Re: Calculation to return result from a specified date?
                  Hari Ankem

                  You can probably create 2 parameters to identify the 2 months and then create calculated fields with LOD's to get you the values for each of the 2 months selected and compute as required. Let me know if you need more help with it.