topic Count number of old/new ID in App Development

Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 14:01:35 GMT

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 DATE	A	count(distinct(if(Extract_Date = MIN 'DATE', ID)) = 8
MIN DATE	B
MIN DATE	C
MIN DATE	D
MIN DATE	E
MIN DATE	F
MIN DATE	G
MIN DATE	H
MAX DATE	A	count(distinct(if(Extract_Date = MAX 'DATE', ID)) = 6
MAX DATE	B
MAX DATE	C
MAX DATE	I
MAX DATE	J
MAX DATE	K

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

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 14:08:43 GMT

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

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 14:13:14 GMT

Hi,

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

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 14:15:23 GMT

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

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 14:21:46 GMT

Check attached

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 14:21:49 GMT

Yes that exactly what I want !

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 14:25:22 GMT

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

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 14:26:37 GMT

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, ', ')

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 14:27:01 GMT

shared the expressions I used

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 14:34:00 GMT

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 ?

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 14:56:41 GMT

Currently I don't, but are you located in the US?

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 15:01:34 GMT

Hi, I have one extrat question. how would you put the expression if I want to sum(sales) in the case that ID in new ?

I tried something like this but no success ...

sum(if(Count({<[ID] = p({<Extract_Date = {"$(=Date(Max(Extract_Date)))"}>})*e({<Extract_Date = {"$(=Date(Min(Extract_Date)))"}>})>}[ID])>0,[Sales])

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 15:03:39 GMT

We are located in South of France. If you come around let us know .

I think also I will ask Qlik partner if they have some local contacts.

Re: Count number of old/new ID

sunny_talwar — Thu, 25 Jan 2018 15:09:48 GMT

Should be just this

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

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

Re: Count number of old/new ID

reivax31 — Thu, 25 Jan 2018 15:25:59 GMT