10 Replies Latest reply: Nov 16, 2010 2:59 PM by John Witherspoon RSS

    Variable expressions

    frost240z

      I have monthly POS data. When I select multiple months I get each months total POS. Is there a way to add a total column at the end that has a sum of all selected months?

        • Variable expressions
          Karl Pover

          If you are using a pivot table you can go to the Presentation tab and select the option to show partial sums for certain columns.

          Is that what you were looking for?

          Regards.

            • Variable expressions
              frost240z

              I see the partial sums but currently the POS data is an expression and it doesn't allow me to select the partial sums for expressions. Let me try and elaborate a little bit more.

              I have customer style, size, and color all being pulled in as fields. I select the customer I want to look at and the time frame (ex. last three months). The POS is done through an expression of a field. This gives me the data by month on my pivot table. I want to add in another column that sums up the total for all the selected months. Is there a way to make an expression sum up another expression for all selected months?

               

              thanks again for all your help.

                • Variable expressions
                  Neil Miller

                  Have you tried putting a partial sum on the last dimension? If month is your last dimension, putting a partial sum there would give you the total for all months.

                  Are your dimensions all on the left or have you moved some across the top?

                    • Variable expressions
                      frost240z

                      The report itself gives me the totals by month. The month ending dates are across the top and all other diminutions and expression are going down the page. In the below example I have everything except for the total column for my selected months. How do I tell the sum expression to include the POS from all selected months and not just the sum of the POS in each month.

                      Sep. Oct. Nov. Total

                      POS 2 5 3 10

                        • Variable expressions
                          Karl Pover

                          I don't understand the difference between the total in the pivot table and the total of the selected months, but you can change the total formula by doing something like the following:

                          if(dimensionality()=0, sum(total Sales), sum(Sales))

                          The total column has a different dimensionality then the other columns. It may not be zero, but a different number. Make an expression dimensionality() to find the dimensionality of each cell and you can use that condition to write a different function for the total column.

                          Regards.

                            • Variable expressions
                              John Witherspoon

                              If your POS expression isn't one that can be summed from the raw data, and must be summed specifically from the monthly results for each style, size and color:

                              sum(aggr(YourPOSExpressionHere,Month,Style,Size,Color))

                              Edit: If your expression for POS is sum(POS)*.95, then you should be able to get a grand total the way everyone has been telling you - do a partial sum on the Month dimension. If that is not working, can you explain what you ARE getting when you do that? What your data looks like? Anything?

                            • Variable expressions
                              frost240z

                              Sep. Oct. Nov. Total

                              POS 2 5 3 10

                               

                              the POS is a monthly total. I am trying to get the total of all selected months (the total column in the above example).

                               

                              POS=Sum([POS])*.95

                              is there a way to create an expression that will sum each months total POS as a grand total POS? I wish i could better explain it. Sorry.

                               

                               

                      • Variable expressions

                        Hi,

                        an easy way is to duplicate your expression (POS data monthly) and check for the new expression the "Full Accumulation" in the presentation tab.

                        Regards, Roland