Pivot table totals disapear when not filtering on a lilst box

[#1]

I am using a pivot table. 4 of my expressions are taking a value that does not need to be summed, 2 of the expressions are.

For the expressions that do not need to to have a sum I am using (sum(aggr((NonSUm Value, 12months))

I am also using a list box, when I select one item from the list box I get all my totals correctly, when I do not filter on any items from the list box my 4 calculations show zero for both the total and the 12months. How can I get this to show the totals in both a filtered and non filtered Please see below for a screen shot to help make things more clear. Keep in mind the screen shots are for the same item just one is selected from the list box and one has no filter

[Oleg_Troyansky]

David,

looks like your expressions are returning no values under certain conditions. If you'd like to avoid totals for some of them, you could use function "DImensionality()" to determine your level of detail and change your expression accordingly. For example:

if(Dimensionality() <3, ' ', sum(Sales) )

This expression will show an empty space for the first 3 levels of subtotals. Notice, that this IF is not too bad - it's only executed once per cell...

cheers,

[#3]

Thanks for your reply,

I just tried that and now I get blanks instead of zeros but really I should have values.

So I tried to find out if I have errors ( as you mentioned) so I removed all the expressions that were: sum(fieldname)

so all I had left was the 4 fields that I am using the sum(aggr(Sum(Sales), 12Months, )) Now I all I get is is the header of the pivot table, no values at all. I know they have values becasue if I add an expression sum(sales) I get my 12 months to display with the same amount in each month. and my 4 other colums are Zeros (including the one that I know have totals for). If I filter on the items I get values for all 12 months.

[#4]

OK I am still working on this issue.

Ok let me try to re-explain things since maybe I was not using the correct terms.

1) I am using a pivot table

2) This pivot table contains 2 dimensions (Company, month) and 2 expressions (Budget and sum(aggr((buget), month)) )

on the second dimensiion (month) I have checked the Show Partial sums

This partial sums totals is where I am having troubles with. If I use the budget field as a straight expression (budget) I will get the correct values for the 12 months and zero for all the records in the expression sum(aggr((buget), month)) ). however I get no total column on the pivot table which is what I want.

If I add a lilst box and add Company to the list box. Then I select one company, I now have a total column, The total that is in the sum(aggr((buget), month)) ) filed is perfect, the 12 month values that were zero in the first example now have the correct values as well, the column budget does not contain a total.

What I need is to have this partial sum total show always and to show values when I am not filtering with a list box.

[johnw]

Still having trouble following, but if you want to aggregate, you have to specify an aggregation expression. In this case, I think you want sum(). So:

budget -> sum(budget)
sum(aggr((budget),month)) -> sum(aggr(sum(budget),month))

[#6]

Ok they say a picture is worth a thousand words

this is the non filtered pivot table. I am using the following

sum(aggr((budget),month))

sum(aggr(sum(budget),month))

budget

when I filer using a list box I get the following, What I want is to have the colum rolling total always have the value 215,535 How can I achieve this. I do not have to aggr function I just want a total of the line.

Now keep in mind that there are 94 records in the result set, so if I do a sum(budget) I will get 94*each month so a sum of the budget is not good.

[johnw]

Here's my understanding of what you're trying to do, and it's working just fine for me, so I guess I don't understand what you're trying to do. Can you modify my example to demonstrate the problem you're having?

In case it's relevant, I also set up a table of Sales to compare to the budget. The sales table has multiple records for each company for each month, but this does not increase the budget because they're in separate tables.

[#8 · Accepted solution]

Yeah I got it to work, I needed to add both dimension to the sum(aggr(budget),month, COMPANY))

I was missing the dimension Company.

Thanks for all who tried to assist. Have a great day,

Next question how do I close (or mark as resolved)?

[johnw]

I think that under the "More" drop down, there should be a "Mark as Answer" or some option like that. In this case, since you solved your own problem, I'd mark your own last post explaining your solution as the answer.