6 Replies Latest reply on Mar 30, 2016 8:04 AM by Tonia Achison

    How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation

    Tonia Achison

      I am having an issue getting my Calculated field to return the Avg of a measure for a set time period that I'm defining dynamically using a parameter.  And my second question is if it's possible to dynamically name a calculated measure somehow?

       

      Let's start with the more major issue first:

       

      To simulate the issue I'm having, I created a sample workbook and report.  Below is an excel that feeds my report and next to it, a sample of what I need to calculate.

       

      I have also attached my workbook.  The first tab uses a parameter to return Timeframe 1, the second tab returns Timeframe 2 (these are used for validation), the 3rd tab uses a Parameter (timeframe-2) and displays two Calculated Measures that are dynamically based on the parameter value.  One works (that uses aggregation SUM), the other that uses AVG does not.  My issue is that I need to use aggregation type AVG for the parameter time period rather than Sum. (Note: You get error "Cannot mix aggregate and non-aggregate comparisons or results in "IF" expressions" if you try to just set the aggregation to AVG for the measure, therefore you must attribute the other values...is this case I tried using ATTR).  When I do that, I don't get a value back.

       

       

      Here are how I defined my Dynamic measures..."DYNAMIC # of In Stocks Timeframe 2" (which uses aggregation type Sum and works) and "DYNAMIC AVG # of Instock's Timeframe 2" (which uses aggregation type AVG and does not return anything).

       

      DYNAMIC # of In Stock's Timeframe 2

      IF [Timeframe-2]='datam' and [Date]>=datetrunc('month',today()) then [Number of In Stock Monthly]

      ELSEIF [Timeframe-2]='c3m' and [Months between today and Date]>=1 and [Months between today and Date]<=3 then [Number of In Stock Monthly]

      ELSEIF [Timeframe-2]='p3m' and [Months between today and Date]>=4 and [Months between today and Date]<=6 then [Number of In Stock Monthly]

      ELSEIF [Timeframe-2]='c6m' and [Months between today and Date]>=1 and [Months between today and Date]<=6 then [Number of In Stock Monthly]

      ELSEIF [Timeframe-2]='p6m' and [Months between today and Date]>=7 and [Months between today and Date]<=12 then [Number of In Stock Monthly]

      ELSEIF [Timeframe-2]='currentm' and [Months between today and Date]=1 then [Number of In Stock Monthly]

      ELSEIF [Timeframe-2]='priorm' and [Months between today and Date]=2 then [Number of In Stock Monthly]

      END

       

       

      DYNAMIC AVG # of In Stock's Timeframe 2

      IF

      [Timeframe-2]='datam' and ATTR([Date])>=datetrunc('month',today()) then AVG([Number of In Stock Monthly])

      ELSEIF [Timeframe-2]='c3m' and ATTR([Months between today and Date])>=1 and ATTR([Months between today and Date])<=3 then AVG([Number of In Stock Monthly])

      ELSEIF [Timeframe-2]='p3m' and ATTR([Months between today and Date])>=4 and ATTR([Months between today and Date])<=6 then AVG([Number of In Stock Monthly] )

      ELSEIF [Timeframe-2]='c6m' and ATTR([Months between today and Date])>=1 and ATTR([Months between today and Date])<=6 then AVG([Number of In Stock Monthly])

      ELSEIF [Timeframe-2]='p6m' and ATTR([Months between today and Date])>=7 and ATTR([Months between today and Date])<=12 then AVG([Number of In Stock Monthly])

      ELSEIF [Timeframe-2]='currentm' and ATTR([Months between today and Date])=1 then AVG([Number of In Stock Monthly])

      ELSEIF [Timeframe-2]='priorm' and ATTR([Months between today and Date])=2 then AVG([Number of In Stock Monthly])

      END

       

      Here is what I see in my workbook:

       

       

      The 4th tab of my report uses Timeframe 1 to calculate the value for the first Dynamic measure, Timeframe 2 to calculate the value of the second Dynamic measure and then MoM between the two. This again needs to use the Avg of the timeframe rather than the Sum (ideally using Avg aggregation for both measures).

       

      My Second question was if it's possible to dynamically name a Calculated measure?  In this case as you can see I named my measures "DYNAMIC # of In Stock's Timeframe 1 or 2".  It would be ideal to be able to name the measure based on the parameter value.  So for instance "Current Month # of In Stock's" and "Current 3 Month # of In Stock's".

       

      Greatly appreciate any assistance you can offer.  Thank you!

        • 1. Re: How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation
          Tonia Achison

          Thank you, I will test this in the morning.  Appreciate your feedback.

           

          ~ Tonia

          • 2. Re: How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation
            Tonia Achison

            In my mocked up example I can switch the aggregation type on the marks card and it works, however in my actual dashboard when I changing the aggregation to AVG on the Marks card the value becomes “1” for each.

             

            Example:  Using aggregation type Sum

             

             

            Here I added Date to the report and I can see that it is adding up correctly for the SUM aggregation:

             

             

             

            Example:  Using aggregation type AVG (all values become 1)

             

             

             

             

            I also tried using set aggregation in the calculation:

            Example:

            IF ='datam'

            and >=datetrunc('month',today()) then

            ELSEIF ='c3m'

            and >=1 and <=3

            then {INCLUDE[Date],[Customer Name]:AVG()}

             

            And then adding it to the report (still using SUM as aggregation on the Marks card) and it Sum’s (that match above) rather than Averages.  When I again change the aggregation on the Marks card for the new measure it gives “1” again.

             

             

            I again appreciate any guideance you can offer.

             

            ~ Tonia

            • 3. Re: How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation
              Tonia Achison

              That approach will not work because I need two different date periods in the report.

               

              Let me try to simplify what I’m trying to do:

               

              1.      I need two parameters for them to select two different date ranges to be applied to the same measure.   (example Timeframe 1 = In Stocks Current Month, Timeframe 2 = In Stocks Current 3 Months)

               

              2.      Due to needing two different timeframe values, I can’t use the filter shelf to control what I “Show”.

               

              3.      This is why I need to create two calculated measures, each using one of the two different timeframe parameters.

               

              4.      The issue I then run into is getting the correct value (average in this case) back in my calculated measure.  If I set it to Sum I get the proper value (summing the current month, and then another measure that sums the current 3 months).  The example would be to compare Timeframe 1 “Current Month” to Timeframe 2 for Current 3 Months (BUT USING THE AVERAGE).  In this example, I want to know how this month is trending compared to the average trend for the last 3, 6, 9 or 12 months.  I cannot get the correct average returned for the timeframe.

               

              5.      I then need to use the two calculations from step 4 above to create a MoM (Month over Month) calc to say we are Up or Down X % based on Timeframe 1 and Timeframe 2 (example:  Current Month is up 5% over the average of the Current 3 Months)

               

              So my key issue is Step #4 above.

               

              Here is an example of my raw date.  Below I set it to aggregate using Sum, and then Avg.  These are the two values I should be  to tying back below for my formula calculations.

               

               

              TEST #1:  Here is a sample report that uses a Time Dimension filter for just one of my timeframes:

               

               

              Here are descriptions of each of the 5 measure values noted above:

               

              1.      Using the raw measure on the report the default aggregation is SUM and we see in column #1 295, which is correct if we are to sum.

               

              2.      Using the raw measure and setting the aggregation in column #2 to AVG gives a value of “1”. – but that’s ok because I can’t simply use this aggregation as I need to ultimately use a calculated measure.

               

              3.      In column #3 “TEST # of In-Stocks”, gives the correct Average I need WHEN I have the Filter set for Timeframe 1 (however remember I need to allow 2 different time frames so I cannot keep on the filter shelf).  Below is how I have it defined:

               

               

               

              4.      In column #4 “Dynamic Timeframe 1 # of In Stock”, gives us the SUM again, rather than average. Here is how I have it defined (note that I do AVG the Number of In Stock Monthly, however it’s really giving me the Sum):

               

               

               

              5.      In column #5 “Dynamic Timeframe 1 # of In Stock”, gives us nothing back.  In this example I am using the formula from #3 above (which gives me the correct Average I need) combined with the formula in #4 above, but in this case when using that same formula along with the timeframes is not working.  Note that I had to add ATTR to each date dimension or I get an aggregation error.

               

               

               

              TEST #2:  Now I remove the timeframe 1 Filter from the Filter shelf and hope that my calculated measures still work.

               

               

               

               

               

              1.      Using the raw measure on the report the default aggregation is SUM and we see in column #1 295, which is correct. –Expected results, summing for all time periods w/out the filter

               

              2.      Using the raw measure and setting the aggregation in column #2 to AVG gives a value of “1”. – but that’s ok because I can’t simply use this aggregation as I need to ultimately use a calculated measure.-Same result as before

               

              3.      In column #3 “TEST # of In-Stocks”.-Expected results, this is NOT the correct average any longer (which should be 98 not 104), however the result is as expected, as there is no way to tie it back to Timeframe 1, so this is giving the average for all time periods.

               

               

               

               

              Below I tested modifying this formula to use my Time Dimension Filter for Timeframe 1 (rather than just Date).  This now give me 418, which is still not correct.  Below is the calculated measure and the timeframe filter value.

               

               

               

               

              Time Dimension Filter for Timeframe 1 (copy 2)

               

               

               

               

              4.      In column #4 “Dynamic Timeframe 1 # of In Stock”, gives us the SUM again, rather than average.  – This still returns the correct value without having to place the filter in the report.  My only issue is that I really need this to be the Average and not the Sum!

               

               

               

              5.      In column #5 “Dynamic Timeframe 1 # of In Stock”, still gives us nothing back.

               

               

               

               

              ~ Tonia

              • 4. Re: How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation
                Tonia Achison

                Even with my sample data I’m not able to get this to work properly.  Here you see it gives me 190 for each customer. I changed Advanced Addressing to use Customer, Date and even my Time Dimension filter and it still gives the average for all customers.

                 

                 

                 

                Here is what it’s doing, rather than just giving the Average (231, 25 and 315):

                 

                 

                SUM

                 

                AVG

                 

                234

                 

                202

                 

                256

                 

                692

                 

                231

                 

                23

                 

                26

                 

                25

                 

                74

                 

                25

                 

                295

                 

                316

                 

                333

                 

                944

                 

                315

                 

                 

                190

                 

                AVG of Averages

                 

                 

                 

                HOWEVER, even if that worked I’m not sure that a solution using a Window Average would work because ultimately I have many measures on the same view and each will need to be calculated at the Customer and Timeframe level.  Here is an example of what the dashboard looks like, mocked up for what I need:

                 

                 

                Here you see that I currently have the CM (Current Month) for that measure displaying and then the MoM % (based on the calculated measure).  Currently I have calculated measures that return the value for the Current Month, another that returns the Value for the Prior Month and then another that does the Current Month over Previous Month to give the %.  I want to make the entire dashboard Dynamic so that they can select a Timeperiod from a Parameter for the first point of comparison, and then a Timeperiod for the second point of comparison that is used in the MoM % Calc.

                 

                The bonus would be everywhere I have CM highlighted, a measure value would allow you to use a dynamic parameter in the name of the calculated measure…so instead of me naming the measure CM % In stock it could use “Timeperiod 1 Value” % In Stock.  And if they select the parameter = Current 3 Months it would display as such.

                 

                Ultimately if they select Timeperiod Comparison 1 = Current Month and Timeperiod Comparison 2 = Current 3 Month Avg, in the background I should calculated the current month value, then the 3 month AVERAGE value, and then the % difference….and do this for each measure.  My dashboard would then only display Timeperiod 1 Measure and my Period 1 over Period 2 Measure.  This will allow them to see how they are doing based on a 3 month average versus a 6 month average, a current month based on a 6 month, etc.  This seems like a relatively simple request, but it’s proven to be very challenging in Tableau.

                 

                ~ Tonia

                • 5. Re: How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation
                  Tonia Achison

                  Yes, correct.  But again, don’t think a window average will work for the overall solution.

                   

                  ~ Tonia

                  • 6. Re: How to use a Parameter (for date period) in a Calculated Measure with AVG Aggregation
                    Tonia Achison

                    I have achieved success at last!  With some additional help from Tableau Technical Support we came up with an approach that will work.  Using a calculated measure and Level of Detail expression and then setting the aggregation within the view.

                     

                    Solve:

                     

                    1.      Create a Calculated Measure

                    Months between today and Date

                    datediff('month',(datetrunc('month',[Date])),(datetrunc('month',today())))

                     

                    2.      Create two parameters (Timeframe-1 & Timeframe-2) to collect dates from the users for the two comparison time periods.

                     

                     

                    3.      Create two Calculated Measures (# of In Stock's Timeframe 1/2), each using the respective parameter (Timeframe-1/Timeframe-2) and this Level of Detail statement which forces aggregation to date and customer for the measure:  {INCLUDE[Date],[Customer Name]:SUM()}  You will notice we still have to use SUM here rather than Average (which is what we need to do in step #4).

                    IF

                    ='currentm' and =1

                    then {INCLUDE[Date],[Customer Name]:SUM()}

                    ELSEIF ='priorm'

                    and =2

                    then {INCLUDE[Date],[Customer Name]:SUM()}

                    ELSEIF ='c3m'

                    and >=1 and <=3

                    then {INCLUDE[Date],[Customer Name]:SUM()}

                    END

                     

                     

                    4.      Set the default aggregation for these two measures to AVGERAGE.  This way when a user selects a timeframe of say “Current 3 months” it will average the total for each of the 3 months (rather than sum).  See the below example:

                     

                    a.      Here I have 3 months and the Grand Total set to Avg.  These are the numbers I want to see when I aggregate.

                     

                     

                    b.      Here I have 4 Measure Values displayed.

                     

                                                                        i.     The first is the raw measure and default aggregation is Sum

                     

                                                                       ii.     The second is the same raw measure but with aggregation set to Avg - Notice the 2nd value gives me 1.  That is because it is filtering the value down to each specific date/customer cell within the table and averaging it, which comes back to 1.

                     

                                                                      iii.     The third is my new Calculated measure (created in step 3 above) – Notice this value is much too high…that is because it’s summing the value over and over for each specific date/customer cell within the table.

                     

                                                  iv.     The fourth is my new Calculated measure (created in step 3 above) WITH its default aggregation set to Average (set in step 4 above).  This is finally what I need.

                       

                     

                    5.        From here I can now create my PoP  (Period over Period) % calculated measures using the above calculations to make truly dynamic calculated measures.

                     

                    6.      My last question was related to creating dynamic column headings.  In the current version of Tableau they do not allow the name of Calculated Fields to be dynamic, however here is a work around:

                     

                    Creating Dynamic Column Headers

                     

                    http://kb.tableausoftware.com/articles/knowledgebase/dynamic-column-headers?userSource=1

                     

                    And here are additional helpful documents on this topic supplied by Tableau Technical Support:

                     

                    Level of Detail Expressions

                     

                    http://onlinehelp.tableau.com/current/pro/online/windows/en-us/calculations_calculatedfields_lod.html

                     

                     

                     

                    Understanding Level of Detail (LOD) Expressions | Tableau ...

                     

                    http://www.tableau.com/learn/whitepapers/understanding-lod-expressions

                     

                     

                     

                    Conceptual Topics with LOD Expressions | Tableau Software http://www.tableau.com/learn/tutorials/on-demand/conceptual-topics-lod-expressions

                     

                     

                     

                    Aggregation and Replication with LOD Expressions | Tableau ...

                    http://www.tableau.com/learn/tutorials/on-demand/aggregation-and-replication-lod-expressions

                     

                     

                    Asterisks Display in Tooltips

                    http://kb.tableausoftware.com/articles/Issue/asterisks-display-in-tooltip?userSource=1

                     

                    ~ Tonia