11 Replies Latest reply on Jan 12, 2018 10:23 PM by Minnu Meena

    Aggregation % cal error

    Minnu Meena

      Hi,

       

      My aggregation calculation is not working.

       

      Kindly please need your assistance.

       

      My requirement is:

       

      I have a column name: Period . It contains Actual and plan

       

      If i select Actual, i should get variance of current year month actual to prev year same month actual.

      If i select plan, i should get current year month actual to current year same month plan.

      For now i used (W Current Mcal Period Code) field to get current month actual value and previous  year month value.

       

      Please find the attached workbook for your reference.

       

      Regards,

      Minnu.

       

       

       

       

       

        • 1. Re: Aggregation % cal error
          Simon Runc

          hi Minnu,

           

          So I think it was just a question of changing the default setting for the Percent Difference Table Calculation.

           

          I set it up like this

          I like to think of this as anything that is "ticked" the calculation runs over, and anything that's not ticked means start the calculation again for each. So in this example it say Run the YoY by Year, and restart for each Month/BU.

           

          Hope that helps.

          • 2. Re: Aggregation % cal error
            Minnu Meena

            Hi Simon,

             

            Thanks for the quick  response.

             

            Actually i have already tried and it is working for only Actuals but not for plan..

            Plan: variance from Current year actuals month to current year plan same month.

             

            I have included the plan calculation in the "same measure flip cal". For actuals the cal working correctly but for plan its not working.

             

            How can i solve this? please help me

            • 3. Re: Aggregation % cal error
              Simon Runc

              It seems to be working for me.

               

              I've taken the table (with Plan selected and Total Billable Hours) into Excel, and then created the same calculation in Excel (for the first BU). This row is in grey, and appears to be the same as what I get in Tableau.

               

              Can you let me know what values you are expecting? and I'll take a look

              • 4. Re: Aggregation % cal error
                Minnu Meena

                Hi Simon,

                 

                Here i have attached the Excel file with the explanation.

                 

                Kindly have a look and please help me on this issue.

                 

                 

                Thanks,

                Minnu.

                • 5. Re: Aggregation % cal error
                  Simon Runc

                  Oh I see...that's a bit of a different problem, as you want Tableau to calculate the YoY variance one way if you are looking at Actual and Another if you are looking at Plan!

                   

                  So first I created 2 new versions of your measure - flip. One for Actual and One for Plan

                  [Measure flip - Actual]

                  case [Measure]

                  when "Corporate Charges" then if  [Period] = "Actual"then [Corp Charge] END

                  when "Direct Cost of Revenue" then if  [Period] = "Actual" then [Dir Cost Rev] END

                  when "Fringe Hours" then if  [Period] = "Actual" then [Tot Fringe Hr] END

                  when "Gross Margin" then if  [Period] = "Actual" then [Gross Margin] END

                  when "Operating Margin" then if  [Period] = "Actual" then [Opr Margin] END

                  when "Total Vacation Hours" then if  [Period] = "Actual" then [Tot Vacation Hr] END

                  when "Operating Profit" then if  [Period] = "Actual" then [Opr Profit] END

                  when "Total Sick Hours" then if  [Period] = "Actual" then [Tot Sick Hr] END

                  when "Total Revenue" then if  [Period] = "Actual" then [Tot Rev] END

                  when "Total Billable Hours" then if  [Period] = "Actual" then [Tot Bill Hr] END

                  when "Total Proposal Hours" then if  [Period] = "Actual" then [Tot Proposal Hr] END

                  when "Total Overhead Hours" then if  [Period] = "Actual" then [Tot Overhead Hr] END

                  when "Total Non Billable Hours" then if  [Period] = "Actual" then [Tot Non Bill Hr] END

                  when "Total G&A Expenses" then if  [Period] = "Actual" then [Tot Ga Exp] END

                  when "Total Hours" then if  [Period] = "Actual" then [Total Hours] END

                  when "Total Holiday Hours" then if  [Period] = "Actual" then [Tot Holiday Hr] END

                  END

                   

                  [Measure flip - Plan]

                  case [Measure]

                  when "Corporate Charges" then if  [Period] = "Plan"then [Corp Charge] END

                  when "Direct Cost of Revenue" then if  [Period] = "Plan" then [Dir Cost Rev] END

                  when "Fringe Hours" then if  [Period] = "Plan" then [Tot Fringe Hr] END

                  when "Gross Margin" then if  [Period] = "Plan" then [Gross Margin] END

                  when "Operating Margin" then if  [Period] = "Plan" then [Opr Margin] END

                  when "Total Vacation Hours" then if  [Period] = "Plan" then [Tot Vacation Hr] END

                  when "Operating Profit" then if  [Period] = "Plan" then [Opr Profit] END

                  when "Total Sick Hours" then if  [Period] = "Plan" then [Tot Sick Hr] END

                  when "Total Revenue" then if  [Period] = "Plan" then [Tot Rev] END

                  when "Total Billable Hours" then if  [Period] = "Plan" then [Tot Bill Hr] END

                  when "Total Proposal Hours" then if  [Period] = "Plan" then [Tot Proposal Hr] END

                  when "Total Overhead Hours" then if  [Period] = "Plan" then [Tot Overhead Hr] END

                  when "Total Non Billable Hours" then if  [Period] = "Plan" then [Tot Non Bill Hr] END

                  when "Total G&A Expenses" then if  [Period] = "Plan" then [Tot Ga Exp] END

                  when "Total Hours" then if  [Period] = "Plan" then [Total Hours] END

                  when "Total Holiday Hours" then if  [Period] = "Plan" then [Tot Holiday Hr] END

                  END

                   

                  And then I dragged the "Quick Table Calc" pill (on Percent Difference) into the Calculation Window...this has the affect of creating an actual calculated field for the variance (the quick table calc is just a short cut, for the most used functions, which you can just write yourself). Once I had this I could adapt it to change how it calculates the variance based on the selection of plan or actual.

                   

                  I've filtered it all down to October and PC Americas so you can check against your Excel.

                   

                  Hope that makes sense.

                  • 6. Re: Aggregation % cal error
                    Minnu Meena

                    Thank you so much for you help Mr. Simon.

                    Here i have applied the period parameter in the same calculation,it is working for Actual but not for plan.

                    Can you please check it once why it is not working for plan?

                    Attaching the Workbook for your reference.

                     

                    Thanks & Regards,

                    Minnu.

                    • 7. Re: Aggregation % cal error
                      Simon Runc

                      So this part is causing the issue

                       

                      when "Total Billable Hours" then if  [Period] = "Plan" and [Month/Quarter]="Month" then

                       

                      From your formula for [Month/Quarter], then when month is selected this field contains the month name (January, February...) and not the string "Month". I've only had a quick look, but I don't think you need the and [Month/Quarter]="Month" as the [Month/Quarter] dimension is doing the aggregation at either Month or Quarter.

                      • 8. Re: Aggregation % cal error
                        Minnu Meena

                        But how come it works for Actual Calculation with the same logic?

                        • 9. Re: Aggregation % cal error
                          Minnu Meena

                          Thank you so much its working.

                          • 10. Re: Aggregation % cal error
                            Simon Runc

                            Glad it's working! Great news

                             

                            It worked for "Actual" as this was using Flip Measure (which didn't have the Month/Quarter check) and not the new [Filp Measure - Actual] and [Flip - Measure - Plan] (which did have this second test on Quarter/Month)

                            • 11. Re: Aggregation % cal error
                              Minnu Meena

                              Thank you Simon. You are here to save our lives.