Qlik Community

Ask a Question

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
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
MVP

Can you share sample data with expected output?

Contributor
Contributor

Having the following data set:

MonthIDVersion
01.January 2011
01.January 2012
01.January 2020
01.January 2021
01.January 2030
01.January 2040
01.January 2050
01.Febuary 2012
01.Febuary 2021
01.Febuary 2040
01.Febuary 2041
01.Febuary 2050
01.Febuary 2060
01.Febuary 2070
01.March 2013
01.March 2041
01.March 2061
01.March 2080

 

I would expect the following result:

DimensionMeasure
January4
Februar2
March1


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
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
Contributor

Hi MB,

thanks for your reply, please see my edit text.

Best regards, 
Marc

Contributor
Contributor

Hi Kush, 

please see the data sample below.

Thanks & Regards, 
Marc

MVP
MVP

Perhaps, something like:

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

tresesco_0-1597908455626.png

 

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

 

Annotation 2020-08-20 114625.png

Contributor
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
MVP

@2Marc  did you try the script solution?