Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following dataset:
FacilityRefNo | TenancyRefNo | TenantName | UnitNo | StreetNo | StreetName | Suburb | Postcode |
100001 | 4823 | MR DAVID SMITH | 1 | 1 | FIRST ST | BLUEBIRD | 2000 |
100001 | 5001 | MISS KELLY JOHNSON | 1 | 1 | FIRST ST | BLUEBIRD | 2000 |
100001 | 5001 | MISS KELLY JOHNSON | 1 | 1 | FIRST ST | BLUEBIRD | 2000 |
100003 | 3006 | MISS LESLEY HUGHES | 13 | KINGFISHER ST | STARLING | 2001 | |
100003 | 3006 | MISS LESLEY HUGHES | 13 | KINGFISHER ST | STARLING | 2001 | |
100003 | 3006 | MISS LESLEY HUGHES | 13 | KINGFISHER ST | STARLING | 2001 | |
100004 | 0606 | MR TONY ADAMS | 12 | WATSON ST | GOTHAM | 2002 | |
100004 | 8787 | MR SIDNEY CROSBY | 12 | WATSON ST | GOTHAM | 2002 | |
100004 | 9999 | MR WAYNE GRETSKY | 12 | WATSON ST | GOTHAM | 2002 |
I would like to add a column to the table that shows the number of TenancyRefNo changes that occur in FacilityRefNo.
FacilityRefNo | TenancyRefNo | Changes | TenantName | UnitNo | StreetNo | StreetName | Suburb | Postcode |
100001 | 4823 | 1 | MR DAVID SMITH | 1 | 1 | FIRST ST | BLUEBIRD | 2000 |
100001 | 5001 | 1 | MISS KELLY JOHNSON | 1 | 1 | FIRST ST | BLUEBIRD | 2000 |
100001 | 5001 | 1 | MISS KELLY JOHNSON | 1 | 1 | FIRST ST | BLUEBIRD | 2000 |
100003 | 3006 | 0 | MISS LESLEY HUGHES | 13 | KINGFISHER ST | STARLING | 2001 | |
100003 | 3006 | 0 | MISS LESLEY HUGHES | 13 | KINGFISHER ST | STARLING | 2001 | |
100003 | 3006 | 0 | MISS LESLEY HUGHES | 13 | KINGFISHER ST | STARLING | 2001 | |
100004 | `0606 | 3 | MR TONY ADAMS | 12 | WATSON ST | GOTHAM | 2002 | |
100004 | 8787 | 3 | MR SIDNEY CROSBY | 12 | WATSON ST | GOTHAM | 2002 | |
100004 | 9999 | 3 | MR WAYNE GRETSKY | 12 | WATSON ST | GOTHAM | 2002 |
I have tried to add a new measure called Tenancy change and use the expression
Sum(Aggr(count(DISTINCT TenancyRefNo),FacilityRefNo))
but it didn't quite give me the output I was expecting at times, also I need to be able to filter on the column as well.
Is anyone able to assist me further here?
Thanks
Thanks for that, worked brilliantly.
Hi,
You have selected the wrong post as an answer, please select the post of Mr.Vegar with the solution. Thank you.
cheers,
Basav