Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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