Count number of old/new ID in barchart

[2Marc]

Dear all, 

I am trying to find a way to calculate new added IDs between two months in a bar chart.

In a similiar manner it already was solved for min/max - date. (See here @sunny_talwar)

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

But in my case I need to use the date dynamically. So, while having the months on dimension, I am searching a formula for my measure like:
=Count({<ID = p({<version= {'0'}>})*e(above({<version= {'0'}>}))>}ID)

This doesn't work. Has anyone have an idea how to solve this?

Thanks a lot in advance!

All the best,
Marc

[Kushal_Chawda]

Can you share sample data with expected output?

[2Marc]

Having the following data set:

Month	ID	Version
01.January 20	1	1
01.January 20	1	2
01.January 20	2	0
01.January 20	2	1
01.January 20	3	0
01.January 20	4	0
01.January 20	5	0
01.Febuary 20	1	2
01.Febuary 20	2	1
01.Febuary 20	4	0
01.Febuary 20	4	1
01.Febuary 20	5	0
01.Febuary 20	6	0
01.Febuary 20	7	0
01.March 20	1	3
01.March 20	4	1
01.March 20	6	1
01.March 20	8	0

I would expect the following result:

Dimension	Measure
January	4
Februar	2
March	1

So what I am trying to do is: 
1. I want to figure out how many IDs are new (version= {'0'}) in one month and 
2. are not in the ID set of the month before (use of p()/e() function in combination of above?)

Problem: Idk how to inser dimension time reference, thats why I wrote the above in bold. Since the approach of @sunny_talwar is fixed to min/max date, but I need a dynamic one-month-shift-reference for the bar chart. 

Thank you very much for your help! 

[miguelbraga]

Hey there,

Can you please describe what you are trying to achieve in more detail. Can't figure what you're trying to achieve.

Best regards,

MB

[2Marc]

Hi MB,

thanks for your reply, please see my edit text.

Best regards, 
Marc

[2Marc]

Hi Kush, 

please see the data sample below.

Thanks & Regards, 
Marc

[tresesco]

Perhaps, something like:

Count(distinct if(Version=0 and not Index(Aggr(NODISTINCT Above( Concat(DISTINCT {<Version={0}>} ID, '|')),Month), ID), ID))

[Kushal_Chawda]

another solution

// assuming your Month field is in proper format
Data:
LOAD 
     Month,
     ID, 
     Version
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Count-number-of-old-new-ID-in-barchart/td-p/1736323]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

New:
NoConcatenate
LOAD *,
     if(Version=Peek(Version) and ID=Peek(ID) and Month<>Peek(Month),1,0) as Version_Flag
Resident Data
Order by Version,ID,Month;

DROP Table Data;

Now you can try below expression

=Count(DISTINCT {<Version={0},Version_Flag={0}>}ID)

[2Marc]

Hi Tresesco, 

thanks a lot for your reply! I'm not quite sure if this could be an solution. The Dates are longer than what I uploaded. So grouping by months are not enough since I have different years as well? Can you explain your solution? Maybe I can adjust it to my needs 🙂

Thanks & Regards,
Marc

[Kushal_Chawda]

@2Marc  did you try the script solution?