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

[mohan_1105]

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

[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.

[mohan_1105]

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]

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]

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

[mohan_1105]

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 · Accepted solution]

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)

[mohan_1105]

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