Help me below requirement?

[gkcchowdary]

Hi

I have fields id and issue_creationdate and taregetdate

Id    Issue_creation_date.    Tareget_date

1.       07/10/2018.                07/15/2018

1 .       07/10/2018.               07/14/2018

2.        07/09/2018.              07/09/2018

3.       07/09/2018.               07/11/2018

3.      07/09/2018.                 07/10/2018

My requirement is count issue id  the  when target date is changed.

Example  above table id 1 first target date 07/14/2018 but that id target date change to 07/15/2018.

So I need calculate issues id intial one targetdate and changed to another date.

How to write the expression? Please help on this.

Thanks

[undergrinder]

Hi Chaitanya,

So, do you want the minimum of target date within the id?

Load

     id,

     min(targer_date)

from/resident

group by id;

At the example you shared with us, I think there can be a case when the target date changed to 07/14/2018 from 07/15/2018. Is there any other field, that indicate the records temporality?

G.

[agigliotti]

maybe this for a KPI expression:

=sum( aggr( if( count(distinct Id&Tareget_date)>1, 1, 0 ), Id) )

Hope it helps.

[undergrinder]

you're right, If the records changed, there are several row for the same ID, no need track the dimension change

I think it's sufficient only count the ID, not the ID&Target date, because the latter is different within ID.

G.

[gkcchowdary]

Yes it is kpi. But your expectation not working.

The above table id 1and 3 target date is changed. So my issue id count is 2.

Just count the issues id when target date is changed.

[agigliotti]

what you get with my expression?

[gkcchowdary]

It's not showing any value

[agigliotti]

let's try using as below:

sum( aggr( if( count(distinct Tareget_date)>1, 1, 0 ), Id) )

of course check if the fields i used are named correctly.

[undergrinder]

The answer should be:

=count(distinct ID)-sum(if(Aggr(count(ID), ID)>1,1,0))

So count the individual ID-s, and subtract from that count, where duplication in ID.

G.