Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan_1105
Partner - Creator III
Partner - Creator III

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

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

Labels (1)
1 Solution

Accepted Solutions
ahaahaaha
Partner - Master
Partner - Master

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

View solution in original post

18 Replies
Anil_Babu_Samineni

May be this

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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mohan_1105
Partner - Creator III
Partner - Creator III
Author

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.

Anonymous
Not applicable

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?

mohan_1105
Partner - Creator III
Partner - Creator III
Author

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.

Anil_Babu_Samineni

1) How you are filtering ?

2) Where these variables are coming

3) What was the usage of variables then

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mohan_1105
Partner - Creator III
Partner - Creator III
Author

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.

ahaahaaha
Partner - Master
Partner - Master

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

Anil_Babu_Samineni

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mohan_1105
Partner - Creator III
Partner - Creator III
Author

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