topic Total month sum in a pivot table in QlikView

Total month sum in a pivot table

Sat, 08 Aug 2009 02:05:16 GMT

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))

Total month sum in a pivot table

johnw — Sat, 08 Aug 2009 02:21:45 GMT

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))

Total month sum in a pivot table

Sat, 08 Aug 2009 03:10:17 GMT

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.

Total month sum in a pivot table

johnw — Sat, 08 Aug 2009 03:30:58 GMT

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.)

Total month sum in a pivot table

Mon, 10 Aug 2009 21:54:26 GMT

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.

Total month sum in a pivot table

johnw — Mon, 10 Aug 2009 23:46:54 GMT

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).