how to get subtotals correct?

[#1]

Hi,

got this simple formula in a pivot chart:

= sum(if([Asset Disposal Period]>=vSelectedPeriod or [Asset Disposal Period]=0,if([Depreciation Year]<vSelectedYear,[Depreciation Value])))

+ (sum(if([Asset Disposal Period]>=vSelectedPeriod or [Asset Disposal Period]=0,[FirstValueOpenBal]))/count(MinMaxAssetKey))

Works fine except for the subtotals being calculated in the wrong way; it seems that QlikView first makes the sum of the individual elements and then recalculates the formula based on the totals to obtain a subtotal.

I should have the sum of the individual results per line as the subtotal. Is this possible? Rather critical for continuing working with QlikView at our company.

Regards,

Gianni

[johnw]

The general expression for getting a "sum of rows" in pivot table subtotals is this:

sum(aggr(YourExpressionHere,YourDimensionsHere))

So as an example, if your dimensions are Customer and Product, and your expression is currently max(InvoiceAmount), replace it with this:

sum(aggr(max(InvoiceAmount),Customer,Product))

[#3]

Hi John,

I have a similar problem with my pivot totals.

Heres my expression used

sum ({<AT_Name={'Mine'}>}ASSET_BALANCES.AB_Quantity)

+

sum ({<AT_Name={'Smelter'}>}ASSET_BALANCES.AB_Quantity * ASSET_PRODUCTS.S_PRIMARY_FEED_PCT /100 / ASSET_PRODUCTS.S_RECOVERY_RATE_PCT *100*-1)

The total sum is correct but when a user filters on AT_Name (either Smelter or Mine from a list box) the total doesnt change but stays the same regardless.  Other list box filters change the total correctly except AT_Name.

Is there something i could do to the expression to allow the total to change upon

AT_Name list box selection.

[swuehl]

You are using a set expression to explicitely set a selection to AT_Name, thus overriding your default selection state.

Not sure if that is what you need and want, but try removing the

{<AT_Name = {'Mine'}>} and {<AT_Name = {'Smelter'}>} from your expression.

But this will almost surely give you different values than now, even if you don't select anything from field AT_Name.

[#5]

Hi, thanks for the reply. The reason AT_Name is used is because there's a different calculation for mine and smelter. If they both had the same calculation it would be easy to remove them.

Any ideas ?

[swuehl]

Ah, I see, I think I have overread that you only have two values for AT_Name.

Try taking care of your default selection state by using the intersection operator * in your set expressions, like

sum ({<AT_Name *= {'Mine'}>}ASSET_BALANCES.AB_Quantity)

+

sum ({<AT_Name *= {'Smelter'}>}ASSET_BALANCES.AB_Quantity * ASSET_PRODUCTS.S_PRIMARY_FEED_PCT /100 / ASSET_PRODUCTS.S_RECOVERY_RATE_PCT *100*-1)

[#7]

Hi Swuehi, yes this worked.  Many thanks.

Ive never used the intersection operator * i will look this up on the help files.  But can you summarize what it does and when i can use in other senarios?

[swuehl]

Well, it's all about set theory, so nothing special if you are familiar with that.

I am not so good at explaining things, but I think here John did a good job (or also here ).