Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Count the value change in a column

Hi,

I have the following dataset:

FacilityRefNoTenancyRefNoTenantNameUnitNoStreetNoStreetNameSuburbPostcode
1000014823MR DAVID SMITH11FIRST STBLUEBIRD2000
1000015001MISS KELLY JOHNSON11FIRST STBLUEBIRD2000
1000015001MISS KELLY JOHNSON11FIRST STBLUEBIRD2000
1000033006MISS LESLEY HUGHES 13KINGFISHER STSTARLING2001
1000033006MISS LESLEY HUGHES 13KINGFISHER STSTARLING2001
1000033006MISS LESLEY HUGHES 13KINGFISHER STSTARLING2001
1000040606MR TONY ADAMS 12WATSON STGOTHAM2002
1000048787MR SIDNEY CROSBY 12WATSON STGOTHAM2002
1000049999MR WAYNE GRETSKY 12WATSON STGOTHAM2002

I would like to add a column to the table that shows the number of TenancyRefNo changes that occur in FacilityRefNo.

FacilityRefNoTenancyRefNoChangesTenantNameUnitNoStreetNoStreetNameSuburbPostcode
10000148231MR DAVID SMITH11FIRST STBLUEBIRD2000
10000150011MISS KELLY JOHNSON11FIRST STBLUEBIRD2000
10000150011MISS KELLY JOHNSON11FIRST STBLUEBIRD2000
10000330060MISS LESLEY HUGHES 13KINGFISHER STSTARLING2001
10000330060MISS LESLEY HUGHES 13KINGFISHER STSTARLING2001
10000330060MISS LESLEY HUGHES 13KINGFISHER STSTARLING2001
100004`06063MR TONY ADAMS 12WATSON STGOTHAM2002
10000487873MR SIDNEY CROSBY 12WATSON STGOTHAM2002
10000499993MR WAYNE GRETSKY 12WATSON STGOTHAM2002

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Try this

count(TOTAL <FacilityRefNo> DISTINCT TenancyRefNo)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

4 Replies
Highlighted
Partner
Partner

Try this

count(TOTAL <FacilityRefNo> DISTINCT TenancyRefNo)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Contributor
Contributor

Thanks for that, worked brilliantly.

Highlighted
Partner
Partner

Glad to be of help.

Have a good day.

/Vegar

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator
Creator

Hi, 

You have selected the wrong post as an answer, please select the post of Mr.Vegar with the solution. Thank you. 

cheers,

Basav