3 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    "UNIQUE VALUE" in Summaries

    Jim Weeks

      I'm using Monarch Pro v 10.0.  When I use UNIQUE VALUE under calculations under Summaries, it works well for the individual unique numbers BUT it will not Subtotal.....the subtotal row reports a null.  There are no nulls in the table field to which the UNIQUE calculation is referring.

       

      Any help would be greatly appreciated!  Thanks!

       

      Jim Weeks

        • "UNIQUE VALUE" in Summaries
          Grant Perkins

          Jim,

           

          Firstly I will confess that this particular aggregation is not one I have any experience with in terms of practical usage and I guess it's the sort of thing that the proposers for the development fully understood in terms of its purpose but most may not. So far I have not found an example of use so my following comments may be well off the mark ...

           

          However, I'll have a go at it.

           

          I suspect that subtotaling is not a practical thing to do for this aggregation (indeed it may have no relevant meaning) and has either been suppressed OR gives the NULL calculation result in all circumstances.

           

          The output of the aggregation can only be a unique value for the current drill level for that row, no value (represented by a blank field) or no unique value (represented by dashes). In effect the aggregation is not performing any arithmetic calculations, just advising on status. In other words it is advising that that row combination of keys only exists once. COUNT with a value of 1 would identify the same line but not the value in the field identified as being Unique.

           

          I don't see any way to select only the summary lines with a 'Unique' value without also getting 'The Rest' summarized with them. However one might get to that stage, export the result to a report file, model the new file to skip 'The rest' lines and then subtotal from what is left. But then I'm not sure what use or purpose the resulting subtotal might have anyway.

           

          Maybe we could look at this from the perspective of what you need to achieve and see if there is another way to get there?

           

           

          HTH.

           

           

           

          Grant

            • "UNIQUE VALUE" in Summaries
              Jim Weeks

              Thank you Grant.....and I should have explained more of my original issue. 

               

              I'll try to expain as best I can......in this particular application (and a number of others we use) we are doing a table lookup to some budget numbers with the purpose in the summary of comparing actual to budget.  The problem has been that there are many more "Actual" detail records than budget records so the budget numbers fields in the table can repeat themselves hundreds of times.  In the summary, what we have done in the past is use an average calculation for the budget field which returns a correct number on the detail summary lines.........but not the summary subtotals which, of course, are averages rather than totals.  That requires us to go into the Excel spreadsheet output and do a bunch of recalculations.

               

              When I saw the "UNIQUE VALUE" calculation, I thought I had the answer.....and it does in fact return the correct budget number on the summary lines but does not total at all.

               

              Any ideas?

               

              Thanks for any help!!

               

              Jim Weeks

                • "UNIQUE VALUE" in Summaries
                  Grant Perkins

                  Hi Jim,

                   

                  OK, so my understanding is that you are looking up a budget value and you can get that to work well enough until you get to a summary line where the calculation will sum the value looked up or otherwise perform an unrequired calculation.

                   

                  If you are only exporting the Subtotal line I would be seriously tempted to export them without the lookup value ansd then re-import THAT file creating a lookup to bring in the external value at that point.

                   

                  You could run this two step process through a script file using a Project to make the activation of it as simple and consistent as possible.

                   

                  Alternatively a summary field using the MAX() aggregation might work for you. So MAX(lookupfield) might do what you want. Or Min().

                   

                  Or I may have missed a nuance in what you need to deliver - it's late here so that is more then likely.

                   

                  HTH.

                   

                   

                  Grant