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

    A way to count records, meeting certain criteria

    Armando Ramirez



      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:


      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:



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


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



          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.