topic Re: Count the value change in a column in QlikView

topic Re: Count the value change in a column in QlikView https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723365#M722618 <P>Glad to be of help.</P><P>Have a good day.</P><P>/Vegar</P> Tue, 30 Jun 2020 05:59:38 GMT Vegar 2020-06-30T05:59:38Z Count the value change in a column https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723350#M722615 <P>Hi,</P><P>I have the following dataset:</P><TABLE width="1222"><TBODY><TR><TD width="121">FacilityRefNo</TD><TD width="119">TenancyRefNo</TD><TD width="282">TenantName</TD><TD width="99">UnitNo</TD><TD width="112">StreetNo</TD><TD width="211">StreetName</TD><TD width="165">Suburb</TD><TD width="113">Postcode</TD></TR><TR><TD>100001</TD><TD>4823</TD><TD>MR DAVID SMITH</TD><TD>1</TD><TD>1</TD><TD>FIRST ST</TD><TD>BLUEBIRD</TD><TD>2000</TD></TR><TR><TD>100001</TD><TD>5001</TD><TD>MISS KELLY JOHNSON</TD><TD>1</TD><TD>1</TD><TD>FIRST ST</TD><TD>BLUEBIRD</TD><TD>2000</TD></TR><TR><TD>100001</TD><TD>5001</TD><TD>MISS KELLY JOHNSON</TD><TD>1</TD><TD>1</TD><TD>FIRST ST</TD><TD>BLUEBIRD</TD><TD>2000</TD></TR><TR><TD>100003</TD><TD>3006</TD><TD>MISS LESLEY HUGHES</TD><TD> </TD><TD>13</TD><TD>KINGFISHER ST</TD><TD>STARLING</TD><TD>2001</TD></TR><TR><TD>100003</TD><TD>3006</TD><TD>MISS LESLEY HUGHES</TD><TD> </TD><TD>13</TD><TD>KINGFISHER ST</TD><TD>STARLING</TD><TD>2001</TD></TR><TR><TD>100003</TD><TD>3006</TD><TD>MISS LESLEY HUGHES</TD><TD> </TD><TD>13</TD><TD>KINGFISHER ST</TD><TD>STARLING</TD><TD>2001</TD></TR><TR><TD>100004</TD><TD>0606</TD><TD>MR TONY ADAMS</TD><TD> </TD><TD>12</TD><TD>WATSON ST</TD><TD>GOTHAM</TD><TD>2002</TD></TR><TR><TD>100004</TD><TD>8787</TD><TD>MR SIDNEY CROSBY</TD><TD> </TD><TD>12</TD><TD>WATSON ST</TD><TD>GOTHAM</TD><TD>2002</TD></TR><TR><TD>100004</TD><TD>9999</TD><TD>MR WAYNE GRETSKY</TD><TD> </TD><TD>12</TD><TD>WATSON ST</TD><TD>GOTHAM</TD><TD>2002</TD></TR></TBODY></TABLE><P>I would like to add a column to the table that shows the number of TenancyRefNo changes that occur in FacilityRefNo.</P><TABLE width="1341"><TBODY><TR><TD width="121">FacilityRefNo</TD><TD width="119">TenancyRefNo</TD><TD width="119">Changes</TD><TD width="282">TenantName</TD><TD width="99">UnitNo</TD><TD width="112">StreetNo</TD><TD width="211">StreetName</TD><TD width="165">Suburb</TD><TD width="113">Postcode</TD></TR><TR><TD>100001</TD><TD>4823</TD><TD>1</TD><TD>MR DAVID SMITH</TD><TD>1</TD><TD>1</TD><TD>FIRST ST</TD><TD>BLUEBIRD</TD><TD>2000</TD></TR><TR><TD>100001</TD><TD>5001</TD><TD>1</TD><TD>MISS KELLY JOHNSON</TD><TD>1</TD><TD>1</TD><TD>FIRST ST</TD><TD>BLUEBIRD</TD><TD>2000</TD></TR><TR><TD>100001</TD><TD>5001</TD><TD>1</TD><TD>MISS KELLY JOHNSON</TD><TD>1</TD><TD>1</TD><TD>FIRST ST</TD><TD>BLUEBIRD</TD><TD>2000</TD></TR><TR><TD>100003</TD><TD>3006</TD><TD>0</TD><TD>MISS LESLEY HUGHES</TD><TD> </TD><TD>13</TD><TD>KINGFISHER ST</TD><TD>STARLING</TD><TD>2001</TD></TR><TR><TD>100003</TD><TD>3006</TD><TD>0</TD><TD>MISS LESLEY HUGHES</TD><TD> </TD><TD>13</TD><TD>KINGFISHER ST</TD><TD>STARLING</TD><TD>2001</TD></TR><TR><TD>100003</TD><TD>3006</TD><TD>0</TD><TD>MISS LESLEY HUGHES</TD><TD> </TD><TD>13</TD><TD>KINGFISHER ST</TD><TD>STARLING</TD><TD>2001</TD></TR><TR><TD>100004</TD><TD>`0606</TD><TD>3</TD><TD>MR TONY ADAMS</TD><TD> </TD><TD>12</TD><TD>WATSON ST</TD><TD>GOTHAM</TD><TD>2002</TD></TR><TR><TD>100004</TD><TD>8787</TD><TD>3</TD><TD>MR SIDNEY CROSBY</TD><TD> </TD><TD>12</TD><TD>WATSON ST</TD><TD>GOTHAM</TD><TD>2002</TD></TR><TR><TD>100004</TD><TD>9999</TD><TD>3</TD><TD>MR WAYNE GRETSKY</TD><TD> </TD><TD>12</TD><TD>WATSON ST</TD><TD>GOTHAM</TD><TD>2002</TD></TR></TBODY></TABLE><P>I have tried to add a new measure called Tenancy change and use the expression</P><P>Sum(Aggr(count(DISTINCT TenancyRefNo),FacilityRefNo))</P><P>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.</P><P>Is anyone able to assist me further here?</P><P>Thanks</P> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723350#M722615 Violent_Democracy 2020-11-25T16:16:04Z Re: Count the value change in a column https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723353#M722616 <P>Try this</P><P>count(TOTAL <FacilityRefNo> DISTINCT TenancyRefNo)</P> Tue, 30 Jun 2020 05:01:37 GMT https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723353#M722616 Vegar 2020-06-30T05:01:37Z Re: Count the value change in a column https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723355#M722617 <P>Thanks for that, worked brilliantly.</P> Tue, 30 Jun 2020 05:18:35 GMT https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723355#M722617 Violent_Democracy 2020-06-30T05:18:35Z Re: Count the value change in a column https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723365#M722618 <P>Glad to be of help.</P><P>Have a good day.</P><P>/Vegar</P> Tue, 30 Jun 2020 05:59:38 GMT https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723365#M722618 Vegar 2020-06-30T05:59:38Z Re: Count the value change in a column https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723370#M722619 <P>Hi, </P><P>You have selected the wrong post as an answer, please select the post of Mr.Vegar with the solution. Thank you. </P><P>cheers,</P><P>Basav</P> Tue, 30 Jun 2020 06:25:01 GMT https://community.qlik.com/t5/QlikView/Count-the-value-change-in-a-column/m-p/1723370#M722619 basav 2020-06-30T06:25:01Z