How To Create a Formula With Different Levels of Aggregation

[#1]

I have the following formula in a Table: =SUM([Amt in LC]) / SUM([Gross Revenue])

The table also includes the following two dimensions: (1) Customer/Parent Name and (2) each Customer's Sold-To's.

I would like the formula to behave as follows:

1. The portion of the formula "SUM([Amt in LC])" is equal to the sum of [Amt in LC] for each Sold To

2. The portion of the formula "SUM([Gross Revenue])" is equal to the total [Gross Revenue] for the Customer/Parent (i.e. the sum of all Sold-To's under that Customer/Parent)

I have attached a picture of a simple mockup (mockup was done in Excel) of what I wish to see. Column E is what I already have. Column F is what I would like to see occur. So, for F2 the formula is $23,605,582.45 / $530,370,140.54 = 4.45%

My question is how do I achieve this in my straight table? I am using QlikView 9 (not sure if it matters).

Any assistance is much appreciated.

Thank you,

Godfrey

[isaiah82 · Accepted solution]

SUM([Amt in LC]) / SUM(TOTAL <[Customer / Parent]> ([Gross Revenue]))

Change [Customer / Parent] if that's not actually your field name.

-Isaiah

[#3]

Thank you Isaiah, that works exactly as I need it to!

Thank you,

Godfrey