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

# AGG Pill to Sum

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

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

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 Airbills Tonnage w/o VC w/o VC FSC w/o VC (incl FSC) VC VC FSC VC (incl FSC) VC - w/o VC VC - w/o VC (incl FSC) % Rev Lost w/VC % Rev Lost w/VC (incl FSC) # Airbills Needed to Make Up Rev Tonnage Needed to Make Up Rev BNA 711 330694.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% 5 2203 CHS 138 57690.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% 1 444 GSO 789 240360.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% 17 5198 Grand Total 1638 628746.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% 21 8003
• ###### 3. Re: AGG Pill to Sum

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

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

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

Hi Ken,

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

Thank you

• ###### 7. Re: AGG Pill to Sum

Hi Ken,

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

Thank you