5 Replies Latest reply: Oct 10, 2011 4:54 PM by Elizabeth Knight RSS

    Tricky Data Range Question

      Hi Everyone,

       

      I am working on a pivot table that will calculate a value, compare that value to a range, and based on where the calculated value falls in the range, display a final value on the report.  I'm going to try to explain this using an example:

       

      I have 4 ranges, one for each 'Type':

       

      Type A Range:
      MinRangeMaxRangeLimit
      025001
      250150002
      500175003
      7501100004

       

      Type B Range:
      MinRangeMaxRangeLimit
      050001
      5001100002

       

      Type C Range
      MinRangeMaxRangeLimit
      030001
      300160002
      600190003
      9001120004

       

      Combined Range
      MinRangeMaxRangeLimit
      020001
      200140002
      400160003
      600180004
      8001100005
      10001120006

       

      The final report should look like this:

      CenterTypeMonth1Month2Total1Total2LimitRemaining
      1A000500022
      1 B000500011
      1C101500021
      1Total101500032
      2A011350021
      2B011350010
      2C000350022
      2Total022350020
      3A000900044
      3B000900022
      3C202900031
      3Total202900053

       

      Where:

      - 'Total1' is a total of the values in 'Month1' and 'Month2' for each 'Type'

      - 'Total2' is a total of production, completely separate from all previous information and is calculated by summing the aggregate of production by Center only, using NoDistinct

      - 'Limit' is to be determined by locating where 'Total2' falls in the ranges above for each type, and using the combined range for the total line.

      - 'Remaining' is 'Limit' - 'Total1'

       

      Right now, I have a lookup table for the ranges, keyed off of 'Type' and with a Sequence Number to identify each line. I have tried several things, and I believe I have gotten the closest with the following:

       

      If(Count({$<"$vTotalProduction" = {">$(=VMSMinRange) <$(=VMSMaxRange)"}>} Distinct VMSSeqNo) = 1, VMSLimit)


      In the above set analysis, vTotalProduction = the same equation used to calculated 'Total2'.  However, because I haven't figured out how to isolate the 'Type' in this set analysis, I have to make a selection in Type to narrow it down, and this still counts all of the range values for that type rather than isolating the one record where vTotalProduction is within the range.

       

      Has anyone ever encountered something like this?  Any help would be greatly appreciated!

        • Tricky Data Range Question
          Jonathan Dienst

          Hi Elizabeth

           

          I would consider using interval matching - one per range type - on Total2. Then I think the problem becomes a lot simpler.

           

          TypeA:

          LOAD * Inline

          [

               AFrom, ATo, AValue

               0, 2500, 1

               2501,5000, 2

          ...

          ];

           

          IntervalMatch(Test)

          LOAD * Resident TypeA;

           

          Now just get the value of AValue, BValue, CValue for Limit.

           

          Hope that helps

          Jonathan

           

           

          Regards

          Jonathan

            • Tricky Data Range Question

              Hi Jonathan,

               

              I believe I know how to do what you're suggesting, and I'm going to try and see if I can get this to work.  However, if possible I would like to avoid having to determine Total2 in the load script.  We have a large dataset already, so I'm trying to do what I can to avoid growing the size of the file.  Thank you for your help!

              • Tricky Data Range Question

                Alright,

                 

                I have tried using interval match in the load script, but I'm afraid it is going to be too cumbersome.  The problem I am running into is that I will need to calculate every possible Total2 amount that could occur from the user's date selection, and then use that in the interval match.  I really need to find away where Total2 can be compared to the range when the selection is made, to avoid loading a lot of extra data into the script.  Are there any other ideas out there?  Thank you for your help!!

                  • Re: Tricky Data Range Question
                    Jonathan Dienst

                    Elizabeth

                     

                    I think he attached example does what you want, except for the combined limits - I have not figured out a way to do them - but this is a start...

                     

                    Regards

                    Jonathan

                      • Re: Tricky Data Range Question

                        Thank you, Jonathan, this was a huge help!  I just had to change a few things to fit my actual data model, but this pulled exactly what I needed for the detail information.  I was not able to get this to work for the total, either, so what I did was add "total" records to my actual data and treated them like normal detail data.  This means I wasn't able to completely avoid adding additional records to the data model, but the size increase was miniscule, especially compared to the other tries I had attempted.

                         

                        Thank you again for working on this!!