Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Partner
Partner

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

1 Solution

Accepted Solutions
Partner
Partner

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

View solution in original post

5 Replies
spividori
Valued Contributor

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:

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

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.

Partner
Partner

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.

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

Thanks,

Mohan !

Partner
Partner

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

Partner
Partner

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

View solution in original post