topic Re: SET ANALYSIS compare string dates in QlikView

SET ANALYSIS compare string dates

Wed, 09 Oct 2013 12:00:38 GMT

Table 1 has date field RES_DATE held as string eg '2012-09-23'. Table 2 has date field PERIOD_START held as string eg '2012-06-01'. How can I use set analysis to select RES_DATE >= PERIOD_START. Tried this count({$<PERIOD_START = {>=RES_DATE}>) but does not work / syntax wrong

Re: SET ANALYSIS compare string dates

lironbaram — Wed, 09 Oct 2013 12:05:33 GMT

hi assuming you want the RES_DATE to be later then the min value of the Period_START

you can use

count({<RES_DATE={">=$(=min(PERIOD_START))"}>} RES_DATE)

Re: SET ANALYSIS compare string dates

MayilVahanan — Wed, 09 Oct 2013 12:06:14 GMT

Convert string field into date or num, then use like below

count({$<PERIOD_START = {"=PERIOD_START >=RES_DATE"}>ExpressionFieldName)

Re: SET ANALYSIS compare string dates

CELAMBARASAN — Wed, 09 Oct 2013 12:06:39 GMT

You can convert it in to Date if you have an option.

Other wise try like below

Count({<UniqueField = {"=Date(Date#(PERIOD_START))>=Date(Date#(RES_DATE))"}>} FieldName)

Re: SET ANALYSIS compare string dates

Gysbert_Wassenaar — Wed, 09 Oct 2013 12:07:36 GMT

You may have another problem too. Set analysis creates a set per chart, not per row. So comparing fields on the same row in a chart in set analysis may not do what you expect. See this document: set_analysis_intra-record.qvw

You can use the date# function to create a numeric date from a string. That allows you to compare string dates by first turning them into numeric dates. It's best to use the date# function in the script to create the numeric dates there. This will add almost no overhead to the reload, but will make things very much easier (and better performing) in the UI.

Re: SET ANALYSIS compare string dates

Wed, 09 Oct 2013 15:42:52 GMT

thankyou

Re: SET ANALYSIS compare string dates

Wed, 09 Oct 2013 15:43:49 GMT

worked great thanks