Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
reivax31
Partner - Creator III
Partner - Creator III

Count number of old/new ID

Hi Qlikers,

I would like to calculate how many old/new ID I have between two dates. What I tried is :

count(distinct(if(Extract_Date = 'MAX DATE', ID)) - count(distinct(if(Extract_Date = 'MIN DATE', ID)) . But this doesn't work for a table like this :


  

MIN DATEAcount(distinct(if(Extract_Date = MIN 'DATE', ID)) = 8
MIN DATEB
MIN DATEC
MIN DATED
MIN DATEE
MIN DATEF
MIN DATEG
MIN DATEH
MAX DATEAcount(distinct(if(Extract_Date = MAX 'DATE', ID)) = 6
MAX DATEB
MAX DATEC
MAX DATEI
MAX DATEJ
MAX DATEK

The result I would like is to find 5 old ID (D,E,F,G,H) in my MIN DATE and 3 new ID (I,J,K) in my MAX DATE. If someone has and idea without doing anything in the script. Just in the visualisation console.

Thanks for your help

1 Solution

Accepted Solutions
sunny_talwar

I will share the expressions

=Count({<ID = p({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})>}ID)

=Concat({<ID = p({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})>}ID, ', ')

=Count({<ID = p({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})>}ID)

=Concat({<ID = p({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})>}ID, ', ')

View solution in original post

14 Replies
sunny_talwar

Is Extract_Date a true date field on is it just MIN_DATE and MAX_DATE?

reivax31
Partner - Creator III
Partner - Creator III
Author

Hi,

It's a true date : in fact i'm using variable :  Extract_Date = date('$(vMinDate)')

sunny_talwar

So between the two dates selected, you would want to know what are ids which stop appearing and ids which came up new?

sunny_talwar

Check attached

Capture.PNG

reivax31
Partner - Creator III
Partner - Creator III
Author

Yes that exactly what I want !

reivax31
Partner - Creator III
Partner - Creator III
Author

I'm using Qlik Sense and .qvw doesn't show me any visualisation. I only see the script.

sunny_talwar

I will share the expressions

=Count({<ID = p({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})>}ID)

=Concat({<ID = p({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})>}ID, ', ')

=Count({<ID = p({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})>}ID)

=Concat({<ID = p({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})>}ID, ', ')

sunny_talwar

shared the expressions I used

reivax31
Partner - Creator III
Partner - Creator III
Author

Splendid !! I would never have found. Is it possible to hire you or invite you for some advance training on Qlik Script and Visualisation Expression ?