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?

If(BILLDATE >= vAPFrom and BILLDATE <= vAPTo,count({<BILLDATE={"$(= '>=' & vBPFrom & '<=' & vBPTo)"}>} distinct CUSTOMER),'There is no Data')

Note: I've checked that, And it will work when the conditions are satisfy

[Anil_Babu_Samineni]

Or remove that static name

If(BILLDATE >= vAPFrom and BILLDATE <= vAPTo,count({<BILLDATE={"$(= '>=' & vBPFrom & '<=' & vBPTo)"}>} distinct CUSTOMER))

[mohan_1105]

Anil,

I checked the QVF you have shared if you take a close look, there is one common field is available in both field which is "ID4" according to the date field you selected. So that the expression should able to display output as "1" for "ID4". Which is what I am looking to do.

[mohan_1105]

Hi Andrey,

Thank you very much. Qlik is getting much interesting for me. Can you please explain me about the selection ID part in the expression.

Count({<BILLDATE={">=$(AnalysisRangeMin)<=$(AnalysisRangeMax)"},  //selection date of analysis

CUSTOMER={$(=Concat({<BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"}>}DISTINCT Chr(39)&CUSTOMER&Chr(39), ','))}  //selection ID

>} DISTINCT CUSTOMER)

[ahaahaaha]

CUSTOMER={$(=Concat({<BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"}>}DISTINCT Chr(39)&CUSTOMER&Chr(39), ','))}

BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"} -  selection dates between min date and max date of base interval

Concat({<BILLDATE={">=$(BaseRangeMin)<=$(BaseRangeMax)"}>}DISTINCT Chr(39)&CUSTOMER&Chr(39), ',') - selection unique ID (DISTINCT CUSTOMER)  with subsequent conversion to a string using function Concat() (the whole environment itself is the maintenance of the function Concat() ).

Let there be a field [MyField] with three lines - ID1, ID2, ID3.

Concat (MyField, ',')  will result in ID1,ID2,ID3. This is not what I must pass into expression of Set Analisys. For the selection of ID, I must pass 'ID1','ID2','ID3'.  In order for Qlik to correctly interpret single quotes, we apply construct Chr(39)&MyField&Chr(39) (Chr(39)&CUSTOMER&Chr(39) in your example) to get the desired result.

Thus, we selected those CUSTOMERS (ID) that are present between BaseRangeMin and BaseRangeMax and their ID were used for selection in the analysis interval.

I hope that the explanation is clear.

Regards,

Andrey

[Anil_Babu_Samineni]

But, As per my condition it will return only Unique Customers when Ranges will happen and then Second range need to work then only return like that it will return.

Note: Please mark helpful if any thing helps to you so that we can look the same and try to figure it out same

may be stalwar1‌ can help us

[sunny_talwar]

Hey Anil -

This post seems to be answered already, isn't it? Not sure how I can help?

[Anil_Babu_Samineni]

Yeah, I haven't seen page. Just reloaded the application then i came to know

[mohan_1105]

Hello Andrey,

Thank you !! Great sense of work