5 Replies Latest reply: May 11, 2017 6:18 AM by Mohan Kumar

# How to count distinct records from two range of dates?

Hello legends,

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 analyse the certain individuals from certain period over specified period?

Hi.

You can create two tables with a date field, one to select from date and the other to select to date.

Table1:

YourDate as DateFrom

resident yourData;

Table2:

YourDate as DateTo

resident yourData;

Then create 2 variables as follows:

vFrom =MinString(DateFrom)

vTo =MinString(DateTo)

And then use the following expression:

Sum(if(yourDate>=vFrom and yourDate<=vTo,yourValue,0))

The expression should be able to do with whole analysis, but I can not make it work.

When you select a DateFrom value and a DateTo value, you should display the data that is between those dates.

I hope this helps you.

Regards.

• ###### Re: How to analyse the certain individuals from certain period over specified period?

Hi.

With set analysis:

=sum({<yourDate={"\$(='>=' & DateFrom& ' <=' & DateTo)"}>} yourValue)

And you do not need to create the variables.

Regards.

• ###### Re: How to analyse the certain individuals from certain period over specified period?

Hi,

The expression works only with one range of value, what I am expecting is select the ID from one range of date and looking whether that ID is turned up in another range of date.

I know that I have to write a set analysis inside the set analysis. I have attached the data and App in this discussion.

Thanks,

Mohan !

• ###### Re: How to count distinct records from two range of dates?

Hello,

Thanks for your heads up. It works perfect for the inclusive date range and It fails when the case like i mentioned in the question.

Thanks again..!!

• ###### Re: How to count distinct records from two range of dates?

The solution for this post is shown in the following thread.

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

Thank you!!