How to count distinct records from two range of dates?

[mohan_1105]

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

[spividori]

Hi.

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

Table1:

load

YourDate as DateFrom

resident yourData;

Table2:

load

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.

[spividori]

Hi.

With set analysis:

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

And you do not need to create the variables.

Regards.

[mohan_1105]

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.

Please help I have no I idea how to write the expression and let me learn.

Thanks,

Mohan !

[mohan_1105]

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

[mohan_1105 · Accepted solution]

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!!