2 Replies Latest reply: Jun 3, 2013 11:19 AM by Adam Haithcox RSS

    Sum and Count for each column

    Adam Haithcox

      With the following two expressions I want to sum all sales from all Ship To's under it's respective Sales Catalog Section as well as count the records of Ship To's for each. So, only include records that had Device sales, records that had Extract sales, etc. I know saying "*" gives me all Ship To's, but I would have expected it to consider the Sales Catalog Section it's under? And yes, I do need the total at each record because I will eventually use these two expressions in another record level expression. What am I missing?

       

      Sum(TOTAL {<[Ship To]={"*"}>} [Sales Amount])

      Count(TOTAL {<[Ship To]={"*"}>} [Ship To])

       

      Capture.PNG

        • Re: Sum and Count for each column
          Stefan Wühl

          You probably don't need to use set analysis here. Try

           

          sum(total<[Sales Catalog Section]> [Sales Amount])

            • Re: Sum and Count for each column
              Adam Haithcox

              That work great swuehl, thank you! I had over simplified it though in my example. I do need to use set analysis I believe because I also need to specify the previous two fiscal years, all months, and all ship to's. WIth your help, this is what I have now that works:

               

              Sum(TOTAL <[Sales Catalog Section]> {$<[Fiscal Year]={'>=$(=FiscalCurrentYear-2)<$(FiscalCurrentYear)'},Month={"*"},[Ship To]={"*"}>} [Sales Amount])

               

              I would also like to include this in a label of an expression although it doesn't seem to break down to Sales Catalog Section like the value does. Any idea why?