Additional expressions at the end of a chart pivot table

[#1]

I'm wondering if something like this is possible. I have a chart set up as a pivot table with 2 dimensions: Item Number and Period Number. I have 1 expression that is Usage History. I have the pivot table set up with the Period as the column label so it looks something like this:

Usage

Item Period Jan Feb Mar Apr Jun...

ABCDE 10 10 15 20 25...

ZYXWV 5 7 8 10 12...

Would it be possible to add another expression to the end of the chart is not part of the column label? For example, if I wanted to add Current Value to the end so it would look like this:

Usage

Item Period Jan Feb Mar Apr Jun... Curr Value

ABCDE 10 10 15 20 25... 40

ZYXWV 5 7 8 10 12... 25

is this possbile?

Thanks.

[villafuerte]

What do you want in last column you want to add? The totals?

[#3]

The last column is the current value expression. I don't want any totals. It would just be another expression. When I add the current value expression it gets put under each Period column which I don't want.

Thanks.

[#4]

I guess there isn't a way to do this???

[johnw]

This isn't exactly what you want, but perhaps you could do something similar. It demonstrates putting pivot table columns into groups with full control over which columns are in which group. So in your case, I guess you'd have a Month group and a Current Value group. The month group would have all the months, and the current value group would just have the current value. There might be a better way to solve it, but that's what I have sitting around on my hard drive.

Actually, you don't even need groups. You just need thirteen different expressions, one for each month (probably using set analysis), then one for the current value. It's a brute force approach, but should do the trick if nobody thinks of a more elegant solution.

[#6]

Thanks John. I'll check into this and see if I can make it work. About your brute force approach, I didn't mention that the months in the chart are dynamic, they can be selected from a list box. There could be one month or any number of months up to twelve. I don't know if that makes a difference to this answer.

[johnw]

Hmmm, yeah, it probably makes a difference. If you brute force it, I think every month will show, whether or not it is selected. Some sorts of set analysis would show values for even the months that aren't selected. Other approaches might show nulls for the months that aren't selected. But I think the columns would always show. Probably not what you want, and I'm not sure that the groups will be any better.

Seems like such a simple and obvious requirement, doesn't it? Well, hopefuly it has a simple answer, even if it isn't obvious to either of us. I'd hate to think that it's really this difficult.

[deepakk]

I hope this application helps you.

[mike_garcia]

After viewing the uploaded examples, I can provide another approach:

I dicovered that, for instance, if you add 'Totals' to the period, that Column will have the dimension value as null, only labeled as Totals. So, you can add an expression like:

=if(isnull(Period), "Expression for current value", "Normal Expression")

This is supposing that there is no Period with an actual value of Null.

Then, you may want to change the Label for Totals in the Presentation tab.

Cons:

- You will not be able to add a Totals Column.

- Your Current Value Column will be highlighted.

Hope this helps you.

Cheers.

[#10]

There is a function under the "inter-record" functions called dimensionality. In its simplest form, it returns the level of aggregation in a pivot table. I have a pivot table where I use this to have a subtotal column display additional information in a separate formula by simply saying "if(dimensionality()=0, formula1, formula2)". In our case, it simply adds a bit of additional information to the same formula, but I suppose it would reference an entirely different calculation but still place the column like a total. Of course, you'll have to make sure you know exactly how many levels of aggregation there are and specify it correctly in the dimensionality argument since the lowest level of aggregation is the highest number returned.