1 Reply Latest reply on Feb 13, 2013 11:36 PM by Jim Wahl

    A way to count records, meeting certain criteria

    Armando Ramirez

      Hello

       

      I have a set of data which has Region, Store, Product as Dimensions and "Sales at Region Level" and "Sales at Store Level" as Metrics.

      If a put everything into a Grid visualization, would look like:

      Image1.bmp

      Then I would like to COUNT at the Region level, how many stores (based on Number of Records or so), are "Sales at STORE level" greater than 0 and how many are below 0 and create a couple of metrics or something which allow me to have a grid that looks like:

       

      Image2.bmp

      Any ideas on how to create the "Records >=0" and the "Records <0" metrics based at the "Sales at STORE level" ??

      Regards

        • 1. Re: A way to count records, meeting certain criteria
          Jim Wahl

          Hi Armando,

           

          The below table calculation will count stores with sales > 0. The FIRST()==0 part prevents overlapping text---the calc is the same for every row in the partition so we only need to display the first.

          IF FIRST()==0 THEN

              WINDOW_SUM(SUM(IF [Sales at STORE Level] >= 0 THEN 1 ELSE 0 END))

          END

           

          Assuming you want region and product in the view, you'll need to have Store on the level of detail shelf. On each table calculation pill, click and select Compute using > Store

          Example at Store Level.png

           

           

          You can use these same table calculations to sum at the region level (the sub-totals above):

          Region Level.png

           

          To be safe here, you want to have Store and Product on the detail shelf. And, on each table calc pill, select Edit Table Calculation > Compute Using > Advanced.  Move Region, Store and Product to the right box (in that order). Click OK and select At the level > Deepest and Restarting every > Region.

           

          See attached.

           

          Jim