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

    Totals for column using lookup function not showing.

    Sjuul Hendricks

      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.

       

      Attached is the packaged workbook. Please share your thoughts.

       

      Thank you in advance!

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

          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:

          2.png

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

           

          1.png

           

          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
          • 2. Re: Totals for column using lookup function not showing.
            Sjuul Hendricks

            Thank you for your help.

             

            This would indeed solve this exact problem, and still make it manageable. Except the actual report needs 4 columns that find some value from another period. year-1 year-2 month-1 period-1. So that would make 5 connections. There are 30 to 35 datasources. So I need about 150 connections instead of 30.

             

            I actually hoped that I could create a field that finds a value based on a query in the same datasource. (like in excel vlookup). And that this column could be summed.