3 Replies Latest reply on Feb 14, 2018 7:18 AM by Justin Melnitsky

    Fiscal Year Dollars Running Sum

    Justin Melnitsky

      Hello Everyone,

       

           I have a table of fiscal period with fiscal year and a dollar amount. So far we have fiscal year 2017 and fiscal year 2018, I want to be able to have a running sum but reset at the turn of the fiscal year. I am aware I can do this using a table calculation, but would like to have this stored as a measure so I can use these numbers in later calculations. This is the formula I have now.

       

      IIF(LOOKUP(SUM(INT([Fiscal Year])),-1) == LOOKUP(SUM(INT([Fiscal Year])),0),RUNNING_SUM(SUM([Capex +  Cor])),SUM([Capex +  Cor])),

       

      Where Fiscal Year is the year and Capex + Cor is the dollar value. The output I am getting is the original dollar value meaning that the expression is returning false. Any ideas?

       

      Thanks in advance

       

      Justin

        • 1. Re: Fiscal Year Dollars Running Sum
          Jim Dehner

          Hi Justin

          First a question - Why are you avoiding "Table Calculations" - lookup is a table calculation that navigates around the underlying table for the viz - if you want to look at last year you will be using some table calculation - i.e. lookup(), previous value(), running sum - all the calculations can be set and reset based on the dimensions in the viz

           

          Now if you really need to avoid table calculations then the approach is to use conditional LOD expressions that incorporate an If statement to check against you fiscal year - not a real practical solution

           

          If you still want to use the formula above you need to lock down the fiscal year in your calculation using an lod set to Max(fiscal year)  If not tableau will look at the fiscal year on each record individually -

          because table calculations are almost last in the order of operation you can use LOD expressions in the table calc but not the other way around

           

          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.

          • 2. Re: Fiscal Year Dollars Running Sum
            Sasha Hanna

            Hi Justin,

             

            For the table calculation to restart at the fiscal year swich the default properties of your date:

            Then set the default scope ("compute using" for each month) and you can recycle the table calculation in other views, just ensure it is still computing by the scope you originally set. You may have to add those pills (year and month) onto rows/columns or the marks card to affect the viz level of detail. Let me know if this helps or please attach a sample workbook representative of the structure of your data.

            Cheers,

            Sasha

            • 3. Re: Fiscal Year Dollars Running Sum
              Justin Melnitsky

              This is exactly what I needed. I was unaware of the Fiscal Date Start feature. Thank you for sharing,

               

              Tootaloo,

              Justin