4 Replies Latest reply: Jul 24, 2012 8:14 AM by Benjamin Dykstra RSS

    Adding a 2nd Column Dimension

      Hello:

       

      I have a straight table that is displaying information about YTD sales, YTD units, YTD COGS, etc. My dimension is year (2012 and 2011 are displayed). Currently I have variance for each of these row items being displayed in the row below.

       

      Year Num                2012               2011

      YTD Sales

      Variance

      YTD Units

      Variance

      YTD COGS

      Variance

       

      Variance is being calculated as follows: ("YTD Sales" - Below("YTD Sales")) / Below("YTD Sales")

       

      What I'm wondering is if there is any way to display variance as a column instead of a row.

       

      Year Num               2012               2011               Variance

      YTD Sales

      YTD Units

      YTD COGS

       

      Any help with this would be greatly appreciated. Also, see the attached file for a look at the table.

        • Re: Adding a 2nd Column Dimension
          Nicole Smith

          Can you post an example of how your data is set up?  It should be possible, but the way to do it depends on your data...

            • Re: Adding a 2nd Column Dimension

              Hi Nicole:

               

              The expressions are layed out like this:

               

              If(Max(Total CCYTD) = 1,

              $(vCCYTDSales) & $(vPCYTDSales),

              $(vMaxCYSales) & $(vMax-1CYSales)

               

              CCYTD is a flag for the current calendar year to date. PCYTD is a flag for the previous calendar year to date.

               

              vCCYTDSales: Sum({$<CCYTD={1}>}SalesDol)

              vPCYTDSales: Sum({$<PCYTD={1}>}SalesDol)
              vMaxCYSales: Sum({$<CYYearNum={$(=Max(Total CYYearNum))}>}SalesDol)

              vMax-1CYSales: Sum({$<CYYearNum={$(=Max(Total CYYearNum-1))}>}SalesDol)

               

              YTD units and COGS are calculated the same way--just sub in Units/COGS for SalesDol. The only difference was for units I had to use a + instead of the & between current and previous and max and max-1. When I tried to use & for this expression it was adding on extra 0s to the total.

               

              Thanks,

               

              Ben D.

                • Re: Adding a 2nd Column Dimension
                  Nicole Smith

                  I'm sorry, but I am still having a hard time picturing how your data is set up.  Is there any way that you can post a sample .qvw file?  (You can strip out data that I don't need to see, and if names or other things need to be scrambled you can do so in Settings > Document Properties > Scrambling, but make sure not to scramble any keys!)  Once I have the .qvw file, I should be able to walk you through how to set up your table.

              • Re: Adding a 2nd Column Dimension

                Hello again and thank you for taking the time to try and help me figure out this problem. I found a solution to this in a similar thread:

                 

                 

                If(Dimensionality(),

                     Money($(vCCYTDSales) & $(vPCYTDSales),'$#,##0'),

                    Num(($(vCCYTDSales) - $(vPCYTDSales) / $(vPCYTDSales), '#.00%'))

                 

                In the presentation tab select show partial sums and variance for the label (for CYYearNum).

                 

                From my understanding dimensionality says: if the value falls within the given dimension then place it there, else use the following for the totals column.

                 

                Thank you again for your help Nicole.

                 

                Ben D.