7 Replies Latest reply on Jun 8, 2018 9:34 AM by gaurang.makadia

    AGG Pill to Sum

    gaurang.makadia

      Hi all,

       

      How can I change pill from AGG to sum of _NonVC $. Please find the attached workbook.

       

      Thank you

       

        • 1. Re: AGG Pill to Sum
          Ken Flerlage

          The formula for _NonVC $ is already an aggregate due to the SUMs in the formula, so changing this to a SUM on the pill is not possible. What is it you're trying to accomplish?

           

          IF ([_NonVCRate]/100)* SUM([Chargeable Weight])< SUM([Rates (Rates)].[Minimum])

          THEN SUM([Rates (Rates)].[Minimum])

          ELSEIF ([_NonVCRate]/100)* SUM([Chargeable Weight])> (SUM([Rates (Rates)].[Maximum]))

          THEN ([_NonVCRate]/100) * SUM([Chargeable Weight])

          END

          • 2. Re: AGG Pill to Sum
            gaurang.makadia

             

             

            Trying to do similar report in the tableau.

            I did calculate all the field example w/o VC = _NonVC but at Airbill

            but need to summarize at the origin level

            Please find attached tableau and excel file

             

             

             

            Excel version        

            Origin# of AirbillsTonnagew/o VCw/o VC FSCw/o VC (incl FSC)VCVC FSCVC (incl FSC)VC - w/o VCVC - w/o VC (incl FSC)% Rev Lost w/VC% Rev Lost w/VC (incl FSC)# Airbills Needed to Make Up RevTonnage Needed to Make Up Rev
            BNA711330694.5$67,266.06$11,435.23$78,701.30$66,820.84$11,359.54$78,180.38($445.22)($520.91)-0.66%-0.66%52203
            CHS13857690.9$11,933.67$2,028.72$13,962.40$11,842.48$2,013.22$13,855.70($91.20)($106.70)-0.76%-0.76%1444
            GSO789240360.9$53,366.56$9,072.31$62,438.87$52,236.81$8,880.26$61,117.07($1,129.74)($1,321.80)-2.12%-2.12%175198
            Grand Total1638628746.3$132,566.29$22,536.27$155,102.56$130,900.13$22,253.02$153,153.15($1,666.16)($1,949.41)-1.26%-1.26%218003
            • 3. Re: AGG Pill to Sum
              Ken Flerlage

              I still don't quite understand. I think my problem is that I don't fully understand your data or what the calculation is trying to accomplish. What numbers do you expect to see in the _NonVC $ column? Can you walk me through a calculation of this value?

              • 4. Re: AGG Pill to Sum
                gaurang.makadia

                Hello Ken,

                 

                Sorry for the confusion. I am trying accomplished is if I have sent a client data summary instead of all the data point in BNA(origin).

                 

                Saying Total NonVC = $ 67,266 for BNA origin similar for GSO and CHS origin.

                So when I click on the _nonVC$ column and it gives me Sum of measure value $67,266 (see screen shot)

                 

                 

                I want to create a table where all the data for _nonVC and _VC are summarized. similar Airbill number and weight (see below screenshot)

                 

                 

                Let me know if you have any additional questions.

                 

                 

                Thank you

                • 5. Re: AGG Pill to Sum
                  Ken Flerlage

                  You are just running into a math problem. Since your calculated fields are all based on aggregated data, then Tableau is aggregating all of it at once when you don't have the air bill number in the view. It's looking at the SUM of the weight, finding that it's huge and thus giving it the [Rates (Rates)].[7500Lb] rate. You want this to evaluate the weight of each air bill first, then aggregate it. To do that, you'd want to remove the aggregation from your calculated fields. However, since you're using blending to get the rates, that's not possible--blending requires aggregation. I think this may require some more significant changes for it to work, unfortunately.

                  1 of 1 people found this helpful
                  • 6. Re: AGG Pill to Sum
                    gaurang.makadia

                    Hi Ken,

                     

                    What could be alternative? I don`t mind changing the data calculation significantly.

                     

                    Thank you

                    • 7. Re: AGG Pill to Sum
                      gaurang.makadia

                      Hi Ken,

                       

                      Can do Union between Tableau server data and excel or any other file type (MYSQL or CSV)?

                       

                      Thank you