1 Reply Latest reply on Nov 7, 2018 8:15 AM by Tanner Mcconvey

    Fixed Sum based on Filters multiplied by a set amount, broken into different category's

    Tanner Mcconvey

      I've gone through many threads on this, and they all seem to not work in my particular scenario. I've been at it the last 5-6 hours off and on.

       

      I have a data set looking at flights. The data comes in on a row by row basis (each flight has its own row). We categorize each flight based on their load factor, so I have a calculated field that separates these into 6 category.

       

      We want to project the number of flights for each category based on the total number of flights for the day. We have set percentages from last summer to do this.

       

      So to begin with i tried the calculated field below, only to figure out that aggregate vs non aggregate can not work:

       

      IF           [LF Category]='Flights with LF <85%' THEN SUM([Flight Y/N (Non ferry)])* .354424

      ELSEIF [LF Category]='Flights with LF >85%' SUM([Flight Y/N (Non ferry)]) THEN .121871

      ELSEIF [LF Category]='Flights with LF >90%' SUM([Flight Y/N (Non ferry)]) THEN .144139

      ELSEIF [LF Category]='Flights with LF >95%' SUM([Flight Y/N (Non ferry)]) THEN .110354

      ELSEIF [LF Category]='Flights with LF >98%' SUM([Flight Y/N (Non ferry)]) THEN .073082

      ELSEIF [LF Category]='Flights with LF 100%' SUM([Flight Y/N (Non ferry)]) THEN .196130

      END

       

      This is a brief example of what the data looks like on a row by row basis. So basically I would like to see it  like the table next to it. Basically looking at our 9 projected flights multiplying that by the projection percent for that category. I made a table corresponding to the data below: 

                     

        

      Another issue I run into is I have filters on top of the data we want to look at.

       

      So basically I want the sum of flights based off what I have filtered for, multiplied by the projection percentage, bucketed into their categories.

       

      Below is a screen shot of the worksheet:

       

       

      I am trying to use these values to overlay this chart below with a line over the bars:

       

       

      Let me know if you need any additional information, I have attached a copy of the workbook with less of the tabs to limit the size:

       

       

      I appreciate any and all help. I have been pulling my hair out all day.