Latest information for a customer only

[#1]

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 Name	Loss Date
A12345	Joe Bloggs	20/08/10
A12345	Mr Joe Bloggs	20/08/10
B23456	Mrs Wright	30/10/10
C34567	Mr Wrong	22/12/10
C34567	Mr John Wrong	22/12/10
D45678	Mrs Smith	05/02/11

Account Name	Date of Account Name change
Joe Bloggs	06/02/05
Mr Joe Bloggs	08/04/09
Mrs Wright	02/05/04
Mr Wrong	01/05/06
Mr John Wrong	03/07/08
Mrs Smith	05/05/05

[jedgson]

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

[swuehl · Accepted solution]

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