Calculating Totals

Follow

Stefano Laera

• March 10, 2022 02:02
• Edited

Hi,

I will post the calculation I have so far below and some photos for context.

I have about 80 total site surveys on store locations. All of the locations fall into a specific program type: (F&P, Lease, ADPI, Special, Facilities). And each survey has a classification for interior conditions, but I am mainly focused on those that are selected as "Major Repairs". The interior conditions are a multi-select category with text choices.

I am trying to calculate the total number of site surveys for each given program type (5 different program types) that all have the Interior Condition Type of "Major Repairs". However, I am stuck at trying to get the totals of each program type. Ultimately, I have tried countless different calculations and I always seem to get "0" or "False". 

Originally, "Program Type" category was a (5) option multi-select text category. Then, I broke each (5) categories down into individual single selection categories, thinking that would make things easier but it didn't. Neither allowed me to get the total number of surveys in each program type. I have not gotten as far as trying to then add the conditional statement of only surveys of each type with "Major Repairs".

Any and all help would be much appreciated!

 

var label1 = "ADPI Survey"
var label2 = "Facilities Survey"
var label3 = "F&P Project"
var label4 = "Lease"
var sum = "0"

var value1 = (@All of Program with nulls === "ADPI")
for(var i=0; i<value1; i++){
if(value1[i]=="ADPI"){
sum += value1[i];
}
}

var value2 = (@All of Program 2 with nulls === "Lease")
for(var i=0; i<value2; i++){
if(value2[i]=="Lease"){
sum += value2[i];
}
}

var value3 = (@All of Program.length === "F&P Project")
for(var i=0; i<value3; i++){
if(value3[i]=="F&P Project"){
sum += value3[i];
}
}

var value4 = (@All of Program.length === "Lease")
for(var i=0; i<value4; i++){
if(value4[i]=="Lease"){
sum += value4[i];
}
}

var markdown = "Attribute | Condition | Amount | Costs \n --- | ---\n";
markdown += label1 +" | "+ value1 + "\n";
markdown += label2 +" | "+ value2 + "\n";
markdown += label3 +" | "+ value3 + "\n";
markdown += label4 +" | "+ value4 + "\n";

markdown

1

• 

  Rainer Grabowski

  • March 12, 2022 12:40

  Hi Stefano,

  Sorry to say that, but your calculation doesn't make any sense. What's the purpose of your vars value (1-4)?
  a) FYI: ".length" always returns a number. So if @All of Programm pulls 80 items  @All of Program.length returns 80
  b) === compares values. So you compare e.g. 80 with "Lease".
  c) So you don't get a real value for all of your vars value 1-4.
  d) If you want to check if a specific category (e.g. Lease) is selected in a multi-select category field you can't use "if equal to" (category[i] == "Lease") because there can be 2 options selected, but you need "it contains" (category[i].indexOf("Lease) > -1)
  e) So you for() loop doesn't work because value (1-4) must be a number, e.g. @all of program.length
  f) You have 4 column headers "Attribute | Condition | Amount | Costs" but only 2 columns --- | ---

  Maybe this code helps you: 

  var i,j;
  var conds = @All of Interior Condition;
  var prog = @All of Program;
  var progs = [];
  for(i = 0; i < prog.length;i ++){
      progs.push(prog[i]);
  }
  var progs = progs.join().split(",");
  var programs = progs.filter(function(item, pos) {
  return progs.indexOf(item) == pos;
  });
  
  var progRows = [];
  for(i = 0; i < programs.length; i++){
      progSum = 0;
      for(j = 0; j < prog.length; j++){
          if(programs[i].indexOf(prog[j]) > -1  && conds[j].indexOf("Major Repairs") > -1){
             progSum += 1
          }
      }
      progRows.push(programs[i] + " | " + progSum +  " | | " );
  }
  
  var header = "Attribute | Condition | Amount | Costs\n"
  var colums = "--- | --- | --- | --- \n";
  var table = header + columns + progRows.join("\n");
  table

  The table will show the program names in the first column (under "Attribute") and in the second column (under "Condition") the number of items where that program and "Major Repairs" is selected. The other 2 columns ("Amount" and "Costs") will be empty because you don't pull the values with @All of field name. 

  You wrote: Originally, "Program Type" category was a (5) option multi-select text category.
  That means for me that in one item multiple programms can be selected. Correct? 

  This part:

  var progs = [];
  for(i = 0; i < prog.length;i ++){
      progs.push(prog[i]);
  }
  var progs = progs.join().split(",");
  var programs = progs.filter(function(item, pos) {
  return progs.indexOf(item) == pos;
  });

  collects first all selected programs from all items and then (in var programms) removes all duplicates so that you get an array of unique program names: maximum 5, but can be less if one program isn't selected in any item. Means: In the table you won't get that program listed, only programs with minumum one selection in the category "Program" will be listed. The advantage of this approach is that you don't need to change the calculation when you change the program names in the category field or if you add/remove one. But if you want all programs to be listed you can replace that part with 

  var programs = ["Facility Survey","ADPI Survey","Lease","F&P Project","Special Request"]

  Rainer

  0

  Comment actions Permalink

Please sign in to leave a comment.