Total month sum in a pivot table

[#1]

Hello Friendlies,

I've tried many o things but I can't seem to get it to work. In the percent column I'm trying to do the following: the (amount for the day / total amount for the month). I'm having difficulties getting total month formula to work properly.

Here is my formula for the amount for that day:

sum( {$<OeTyp={"S","W"},OrdTyp-={"4"}> }Tot_Invoice - (TotFedTax-TotSalesTax))

[johnw]

Well, for simplicity, let's say your Amount expression was just sum(Amount). Then the percentage of the total for the month would be sum(Amount)/sum(total <Month> Amount).

So I THINK that for your actual case, you'd want this:

sum( {<OeTyp={"S","W"},OrdTyp-={"4"}> }Tot_Invoice - (TotFedTax-TotSalesTax))
/sum(total <Month> {<OeTyp={"S","W"},OrdTyp-={"4"}> }Tot_Invoice - (TotFedTax-TotSalesTax))

[#3]

Thanks for your reply,

but that's giving a very large number for Total Month Amount and the amount is the same for every month.

[johnw · Accepted solution]

Well, if your Month values don't include the year in them (mine normally do), you'd need to do <Year, Month> instead of just <Month>. But that's probably not the problem, since you'd have smaller numbers than expected if that was the problem, not larger. With that corrected, I'm getting good numbers for the attached sample file.

(Edit: Ah, I missed that you have Branch in there as a dimension as well, and you called year FY. So I'd think you'd want <FY, Month, Branch> in the total expression.)

[#5]

John,

that's beautiful! You were right on the money on that. I've never really use the SUM TOTAL expression before and I thought SUM would already take into the account the dimensions in the table.

[johnw]

Sum() DOES take into account ALL dimensions in the table. Sum(total ...) IGNORES all dimensions in the table. But in your situation, you wanted to take some dimensions into account, but not others. Fortunately, sum(total ...) lets you specify which dimensions you want to account for (something I kept forgetting for my first year or two of QlikView).