topic How to count distinct record with the condition match two diff. date range? in App Development

How to count distinct record with the condition match two diff. date range?

mohan_1105 — Sat, 16 Nov 2024 09:47:55 GMT

Hello,

I am very new to Qlik I need your help on this.

I have long range of transaction data, I wanted to count the customer who is in the range of date1 and I wanted to count them if there are exist in another date range. I have a data like this,

ID Date

7 30-12-2015

2 1-1-2016

3 1-1-2016

1 2-1-2016

4 2-1-2016

4 3-1-2016

1 3-1-2016

8 4-1-2016

9 14-1-2016

1 15-1-2016

2 15-1-2016

2 17-1-2016

1 18-1-2016

Expression should satisfy in the following case,

Case (Exclusive Date Range):

Base Date Range1: 01-01-2016 to 02-01-2016

Analysis Date Range2: 15-01-2016 to 18-01-2016

From the base period range, Expression will select the ID's 1,2,3,4 then I need to count the ID's in analysis period date which matches with the Base period ID's. i.e. the matching value is 1,2. So I need the output as 2 (count of ID).

I know I have to write the set analysis, but I am new to Qlik. I tried my best but still, I cannot get the expression. I have attached my sample data and .qvf with the attempt I took in this discussion. Please help and let me learn Qlik.

Thanks,

Mohan

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 07:20:09 GMT

May be this

Count({<BILLDATE = {'>=$(=min(BILLDATE)) <= $(=Max(BILLDATE))'}>} distinct CUSTOMER)

And This Is qlikview Branch , You must move to QS Branch.

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 07:35:50 GMT

Hi,

Thank you, Anil. It works only in case of inclusive date eg. base period date range is 10-02-2016 to 01-05-2016 and Analysis date range is 03-03-2016 to 05-04-2016 (this range is inside the other date range). While in the case liIe I mentioned in the question, the expression isn't giving the expected outcome.

Re: How to count distinct record with the condition match two diff. date range?

Anonymous — Thu, 11 May 2017 07:45:03 GMT

Hi Mohan,

How do you define the Base Date Range 1 and Analysis Date Range 2.

Is there any specific condition to derive the date ranges?

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 07:53:28 GMT

Hi,

Those fields are just the variable, which is independent to choose. There are no conditions applied to the date field. Also, please a look at the App, I believe I achieved the half mark to get the expression.

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 08:21:46 GMT

1) How you are filtering ?

2) Where these variables are coming

3) What was the usage of variables then

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 08:25:36 GMT

1) I cannot apply Filter pane on the date field, So I wouldn't able to filter.

2) These Variable are coming from Dimension (BILLDATE) and I am using these variables using Qlik sense extension.

3) I gonna use these variables inside my set analysis.

Re: How to count distinct record with the condition match two diff. date range?

ahaahaaha — Thu, 11 May 2017 08:35:42 GMT

Hi Mohan,

Example in attached file. As input, I took the data from your post. Comments can be seen in the expression editor of the result indicator.

Regards,

Andrey

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 08:37:19 GMT

Got it now. May be use this?

Static

count({<BILLDATE={"$(= '>=' & '24-06-2016' & '<=' & '27-06-2016')"}>} distinct CUSTOMER)

With Variable

count({<BILLDATE={"$(= '>=' & vBPFrom & '<=' & vBPTo)"}>} distinct CUSTOMER)

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 08:59:39 GMT

Hi Anil,

This expression works absolutely good when I want to count the record using one range of date. As I mentioned in Question, I wanted to count the record in date range 2, if any record in the table which in date range 1. Please check the case I mentioned in Question.

Thank you

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 09:10:03 GMT

May be this?

If(BILLDATE >= vAPFrom and BILLDATE <= vAPTo,count({<BILLDATE={"$(= '>=' & vBPFrom & '<=' & vBPTo)"}>} distinct CUSTOMER),'There is no Data')

Note: I've checked that, And it will work when the conditions are satisfy

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 09:11:16 GMT

Or remove that static name

If(BILLDATE >= vAPFrom and BILLDATE <= vAPTo,count({<BILLDATE={"$(= '>=' & vBPFrom & '<=' & vBPTo)"}>} distinct CUSTOMER))

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 09:37:47 GMT

Anil,

I checked the QVF you have shared if you take a close look, there is one common field is available in both field which is "ID4" according to the date field you selected. So that the expression should able to display output as "1" for "ID4". Which is what I am looking to do.

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 10:11:00 GMT

Hi Andrey,

Thank you very much. Qlik is getting much interesting for me. Can you please explain me about the selection ID part in the expression.

Count({<BILLDATE={">=$(AnalysisRangeMin)<=$(AnalysisRangeMax)"}, //selection date of analysis

CUSTOMER={$(=Concat({<BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"}>}DISTINCT Chr(39)&CUSTOMER&Chr(39), ','))} //selection ID

>} DISTINCT CUSTOMER)

Re: How to count distinct record with the condition match two diff. date range?

ahaahaaha — Thu, 11 May 2017 10:56:34 GMT

CUSTOMER={$(=Concat({<BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"}>}DISTINCT Chr(39)&CUSTOMER&Chr(39), ','))}

BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"} - selection dates between min date and max date of base interval

Concat({<BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"}>}DISTINCT Chr(39)&CUSTOMER&Chr(39), ',') - selection unique ID (DISTINCT CUSTOMER) with subsequent conversion to a string using function Concat() (the whole environment itself is the maintenance of the function Concat() ).

Let there be a field [MyField] with three lines - ID1, ID2, ID3.

Concat (MyField, ',') will result in ID1,ID2,ID3. This is not what I must pass into expression of Set Analisys. For the selection of ID, I must pass 'ID1','ID2','ID3'. In order for Qlik to correctly interpret single quotes, we apply construct Chr(39)&MyField&Chr(39) (Chr(39)&CUSTOMER&Chr(39) in your example) to get the desired result.

Thus, we selected those CUSTOMERS (ID) that are present between BaseRangeMin and BaseRangeMax and their ID were used for selection in the analysis interval.

I hope that the explanation is clear.

Regards,

Andrey

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 11:11:13 GMT

But, As per my condition it will return only Unique Customers when Ranges will happen and then Second range need to work then only return like that it will return.

Note: Please mark helpful if any thing helps to you so that we can look the same and try to figure it out same

may be stalwar1‌ can help us

Re: How to count distinct record with the condition match two diff. date range?

sunny_talwar — Thu, 11 May 2017 11:13:40 GMT

Hey Anil -

This post seems to be answered already, isn't it? Not sure how I can help?

Re: How to count distinct record with the condition match two diff. date range?

Anil_Babu_Samineni — Thu, 11 May 2017 11:19:14 GMT

Yeah, I haven't seen page. Just reloaded the application then i came to know

Re: How to count distinct record with the condition match two diff. date range?

mohan_1105 — Thu, 11 May 2017 12:09:28 GMT

Hello Andrey,

Thank you !! Great sense of work