topic Re: Set analysis using date range? in QlikView

Set analysis using date range?

Mon, 15 Apr 2013 16:06:18 GMT

I have a requirment to count the number of customers that were contacted between two date ranges, I guess this can be done using set analysis however I am having problems with the syntax. I appreciate any help?

Basically I have a field named CONTACT_MADE which holds date value in the following format 'DD/MM/YYYY' I need to count how many customers were contacted between two date ranges e.g. between 01/01/2010 and 31/12/2012 ? (note: multiple rows per customer)

I appreciate any advice.

Thank you

Re: Set analysis using date range?

Gysbert_Wassenaar — Mon, 15 Apr 2013 16:51:58 GMT

Easiest to create two variable, i.e vStartDate and vEndDate, and use those in the expression. Make sure the variables contain numeric dates. For example

vStartDate: date#('01/01/2010','DD/MM/YYYY')

vEndDate: date#('31/12/2010','DD/MM/YYYY')

The expression will then look something like:

count({<CONTACT_MADE={'>=$(vStartDate)<=$(vEndDate)'}>} distinct Customer)

Re: Set analysis using date range?

Anonymous — Mon, 15 Apr 2013 16:52:53 GMT

For this specific date range:

count({<CONTACT_MADE={"$(= '>=' & '01/01/2010' & '<=' & '31/12/2012')"}>} distinct customer

In a more generic form, where you can use expressions for dates
count(<CONTACT_MADE={"$(= '>=' & date(startdate expression) & '<=' & date(enddate expression))"}>} distinct customer

Regards,
Michael

Re: Set analysis using date range?

usamabinsadiq — Mon, 06 Apr 2020 13:46:25 GMT

Thanks, it helped me too.