# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for
Search instead for
Did you mean:
Contributor

## Count number of old/new ID in barchart

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

10 Replies
MVP

Can you share sample data with expected output?

Contributor

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!

Specialist III

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

Contributor

Hi MB,

thanks for your reply, please see my edit text.

Best regards,
Marc

Contributor

Hi Kush,

please see the data sample below.

Thanks & Regards,
Marc

MVP

Perhaps, something like:

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

MVP

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)``

Contributor

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

MVP

@2Marc  did you try the script solution?