Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you share sample data with expected output?
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!
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
Hi MB,
thanks for your reply, please see my edit text.
Best regards,
Marc
Hi Kush,
please see the data sample below.
Thanks & Regards,
Marc
Perhaps, something like:
Count(distinct if(Version=0 and not Index(Aggr(NODISTINCT Above( Concat(DISTINCT {<Version={0}>} ID, '|')),Month), ID), ID))
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)
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
@2Marc did you try the script solution?