Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mohan_1105
Contributor 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

1 Solution

Accepted Solutions
ahaahaaha
Honored Contributor

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

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

18 Replies

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

May be this

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

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

Life is so rich, and we need to respect to the life !!!
mohan_1105
Contributor III

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

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.

sharanyasenthil
New Contributor III

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

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

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

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?

1) How you are filtering ?

2) Where these variables are coming

3) What was the usage of variables then

Life is so rich, and we need to respect to the life !!!
mohan_1105
Contributor III

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

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

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

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?

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)

Life is so rich, and we need to respect to the life !!!
mohan_1105
Contributor III

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

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