2 Replies Latest reply: Jul 14, 2011 12:59 PM by Stefan Wühl RSS

    Latest information for a customer only

      Hello,

       

      I am trying to capture information in a straight table that will show the amount of real losses for our system. Basically if I do a count of the below customers who have left my company it will say I have lost 4 (this is based on a distinct count of the meter nos.), however if I put this information into a table box or straight table it will bring up 6 records due to the account name being different on 2 of the meter nos. I have provided another table that links to the first table and this shows the date of which the account names were changed.

       

      So in a straight table I really want to only show the losses with the most up to date account name, e.g. for Meter No, A12345 I want to see that only Mr Joe Bloggs left my company on 20/08/10, rather than Mr Joe Bloggs and Joe Bloggs leaving on the same date. I'm hoping that something can be done with the date of account name change field.

       

      If you need any clarification around this just ask.

       

      Thanks,


      Ralph

       

       

      Meter No.Account NameLoss Date
      A12345Joe Bloggs20/08/10
      A12345Mr Joe Bloggs20/08/10
      B23456Mrs Wright30/10/10
      C34567Mr Wrong22/12/10
      C34567Mr John Wrong22/12/10
      D45678Mrs Smith05/02/11

       

      Account NameDate of Account Name change
      Joe Bloggs06/02/05
      Mr Joe Bloggs08/04/09
      Mrs Wright02/05/04
      Mr Wrong01/05/06
      Mr John Wrong03/07/08
      Mrs Smith05/05/05
        • Latest information for a customer only

          In a straight table you could add a calulated dimension along the lines of:

           

          if(not isnull(aggr(max([Date of Account Name change]), [Meter No])), [Account Name])

          and then ensure you tick the option 'Suppress When Value Is Null'

           

          Then add a expression of '=1'.  You can then hide the expresssion column on the presentation tab.

           

           

          The other way would be to create a flag in the script that shows which Account name is the latest.

          Create a mapping table by grouping your second table above by Account Name and Max(Date Of Account Name Change)  Map this back into the second table.  You can then compate the 2 dates and show which is the latest.

           

          Regards

          Jay

          • Re: Latest information for a customer only
            Stefan Wühl

            Hi Ralph,

             

            I think this problem is one of the kind "slowly changing dimension".

             

            You might consider to have a look at the intervalmatch function.

             

            But probably this is too much for that kind of your problem.

             

            You may try as expression

            =FirstSortedValue([Account Name], -1*date#([Date of Account Name change],'DD/MM/YY'))

             

            To get only the latest account name.

             

            Regards,

            Stefan

             

            P.S. date# is only needed in case [Date of ...] is not recognized as date format (as on my system).

             

            Nachricht geändert durch swuehl