2 Replies Latest reply on Oct 15, 2014 12:06 AM by Sjuul Hendricks

# Totals for column using lookup function not showing.

Hello all,

I am having a problem with the subtotals and grand total in a column that uses the lookup function.

What I want is:

A cross chart with 2 columns.

On the rows Year and Week.

One column that shows the value for that period (year week).

One column that shows the value for the same period last year (year-1 week).

In the workbook you find:

[Value] data value. 1 value for each year-week.

[Value this year] shows [Value] if it fits the selection.

[Value last year] shows [Value] if it fits the selection. But one year before selection.

[Value last year on this year] shows [Value last year] of 52 lines above using lookup.

I hide the rows that I don't need with [ShowLine]. If you delete it from filters, the whole will be visible.

[Value last year on this year] shows the correct value, but the subtotals aren't showing/working.

• ###### 1. Re: Totals for column using lookup function not showing.

Sjuul,

The reason for the behavior you are seeing is that the grand total lines are calculated separate from everything else.  When you use a table calculation such as LOOKUP, you will often see that nothing appears in the total row because it is a single row.  There are no rows before it or after it -- so nothing to lookup.at any offset, except 0.

Now, I love table calculations.  They can solve almost any problem and we could even make it work in this case (see Jonathan Drummey's excellent post here to see see how to customize grand totals: Customizing Grand Totals – Part 1 | Drawing with Numbers - be sure to check out the subsequent parts too).

But in this case, I might suggest using data blending on a copy of the data source as an alternative that is simpler and maybe slightly more intuitive.  The key is that we'll create a calculated field in the primary source that gives us the value for last year at a row level:

We can then use that to blend to a copy of the data source making sure to set the relationship so we blend from Last Year (in the primary) to Year (in the secondary).

That gets us this year's and last year's values together.

I've attached your workbook so you can see it.  Please let me know if you have any questions!

Regards,

Joshua

1 of 1 people found this helpful