6 Replies Latest reply: Mar 18, 2011 12:12 PM by Trent Jones RSS

    Set Analysis Help !!!

      Hi All

      I want this expression to sum all [Claim Transaction Payments] where the [Claim Transaction Sequence Number] is = to the Maximum [Claim Transaction Sequence Number]

      sum({$<[Claim Transaction Sequence Number] = {'max[Claim Transaction Sequence Number]'}>} [Claim Transaction Payments])

      If you can think of an alternative method that would be great.

      Many thanks in advance

        • Set Analysis Help !!!
          Miguel Angel Baeyens de Arce


          I'd use something like


          sum({$<[Claim Transaction Sequence Number] = {'$(=max({1} TOTAL [Claim Transaction Sequence Number]))'}>} [Claim Transaction Payments])

          If the result is numeric, then you don't have to single quote the $() part.

          Hope that helps.

            • Set Analysis Help !!!

              Thanks you for your reply

              It is a numeric value. Althogh when i implement your suggested expression, there it returns values of zero for every record.


                • Set Analysis Help !!!
                  Hello! i did some fine tuning on the syntax and got out with 2 suggestions

                  This one will return a value if the max(claim transact seq no)of all records (regarding the current selection) is in the selected or possible values
                  sum({$<[Claim Transaction Sequence Number] = {"=$(=max({1} TOTAL [Claim Transaction Sequence Number]))"}>} [Claim Transaction Payments])

                  This one will return value for the max(claim transact seq no) of the current selection
                  sum({$<[Claim Transaction Sequence Number] = {"=$(=max([Claim Transaction Sequence Number]))"}>} [Claim Transaction Payments])

                  Good luck :)
              • Set Analysis Help !!!

                If you want a different max claim transaction sequence number for every row then you'll have to do something tricky with aggr but you didn't really give enough details to indicate whether that is what you need or if set analysis will work.

                  • Set Analysis Help !!!

                    Here is my data set with an explanation as to what i need to do


                    Your help is much appreciated

                      • Set Analysis Help !!!

                        Well you could just add something like this:


                        LEFT JOIN LOAD

                        [Claim ref],

                        max([Claim Transaction Done Date]) as 'Max Done Date'

                        RESIDENT whateverthetablenameis

                        GROUP BY [Claim ref];


                        LEFT JOIN LOAD

                        [Claim Ref],

                        if([Max Done Date]=[Claim Transaction Sequence Number],1,0) as 'Max Flag'

                        RESIDENT whateveryourtablenameis;



                        Then you just select max flag = 1 or just put it in the set analysis I guess. I'm pretty terrible with the script and joins so it's possible I screwed something up but I've done something similar to this many times and it works for me. Basically you'll get:


                        [Claim ref:] [Done Date] [Amount] [Max Done Date] [Max Flag]

                        A 1/1/2007 50 3/1/2007 0

                        A 2/1/2007 25 3/1/2007 0

                        A 3/1/2007 80 3/1/2007 1



                        I think if you look at the table above it's pretty easy to understand. You could probably do something better with inner join where you wouldn't need a flag but I'd probably mess that up.


                        Another solution that avoids altering the script:

                        Claim ref = Dimension


                        if([Claim Transaction Done Date]=max(total <[Claim ref]> [Claim Transaction Done Date]),
                        ,[Claim ref],[Claim Transaction Done Date]


                        You might need to do something about the format of the [Claim Transaction Done Date] field but I don't know. If you want two transactions on the same day but different times to be added, then you probably need to take the time out with like left([Claim Transaction Done Date],10) as 'Claim Transaction Done Date' and then rename the other one to something with time in it.


                        Hope that helps