1 Reply Latest reply on Jun 29, 2016 2:33 PM by diego.medrano

    Subsetting issues

    Peter Barkat

      Hi Community,

       

      I have been looking around for a while and have not been able to find answers to the following three issues that relate to subsetting. My actual problem relates to insurance where the calculations that I am trying to perform make more sense (there are a lot of cumulative amounts where only the maximum row/ date are required). I cannot share that workbook as it has confidential information, so I have prepared the attached workbook.

       

       

      Issue 1: Multiple Lines:

      For the attached workbook the issue is the following:

      I currently have many lines on a line graph and would only like 4 lines which demonstrate:

      1. the lower quartile (for each month)

      2. the median (for each month)

      3. upper quartile (for each month)

      4. one additional line of the supplier that I choose

      The lines need to be able to flex depending on the supplier and other options chosen.

       

       

      Issue 2: Max supplier

      In the attached workbook the issue is the following:

      This is a hypothetical Situation where I would like to only take the maximum amount of orders from the selected suppliers, and then divide that by the total amount of sales.

      For instance, if only 3M, Acco and Acme were selected, say Acco had the highest amount of orders, the calculation would be total sales (across the state)/ Acco orders.

       

      Actual Issue:

      What I am trying to do with my dataset is find the average amount of a claim, but this is split by bands. When a claim comes through the amount of the claim is split between the bands. For instance, with the three bands with the following ranges:

      band 1: 0- 15k

      band 2: 15k- 45k

      band 3:  45k - 80k

       

      if there is a claim for 50k, then this would result in

       

      AmountCount
      Band 115k1
      Band 230k1
      Band 35k1

       

      the amounts are on an incremental basis, and it is counted as in a band if it gets into the threshold. Any claim that reaches band 3 threshold will also be included in band 2 and 1. If the amount of claim is only 10k then it will only be counted in band 1. If I am trying to find the average amount of the claim then I need to have the count equal to the lowest band value that is selected (equivalent to taking the maximum count of the selected bands).

       

       

      Issue 3 Final Dates:

      For the attached workbook: I would like to have the functionality where if the month is chosen, it would take the average sales from the last week of that month.

       

      In insurance many of the numbers are cumulative on a quarterly basis. I would like the ability to change to a yearly view, and when I do, to take the latest quarter of the year.

       

       

      I would be happy to provide any other information,

      Thanks,
      Peter

        • 1. Re: Subsetting issues

          Hey Peter,

           

          Thanks for asking such a thorough question. There's a lot to unpack here so it might overwhelm a potential helper. I'd recommend breaking this off into multiple questions. In general, one question per post will yield the best result.

           

          Thanks,

           

          -Diego