Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gkcchowdary
Creator
Creator

Help me below requirement?

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

8 Replies
undergrinder
Specialist II
Specialist II

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
Partner - Champion
Partner - Champion

maybe this for a KPI expression:

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

Hope it helps.

undergrinder
Specialist II
Specialist II

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
Creator
Creator
Author

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
Partner - Champion
Partner - Champion

what you get with my expression?

gkcchowdary
Creator
Creator
Author

It's not showing any value

agigliotti
Partner - Champion
Partner - Champion

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
Specialist II
Specialist II

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.