topic Re: How to analyse the certain individuals from certain period over specified period? in QlikView

How to count distinct records from two range of dates?

mohan_1105 — Fri, 05 May 2017 09:59:00 GMT

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?

spividori — Fri, 05 May 2017 13:55:23 GMT

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.

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

spividori — Fri, 05 May 2017 14:22:18 GMT

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?

mohan_1105 — Wed, 10 May 2017 14:44:16 GMT

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 !

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

mohan_1105 — Wed, 10 May 2017 17:35:33 GMT

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?

mohan_1105 — Thu, 11 May 2017 10:18:58 GMT

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