SET ANALYSIS compare string dates

[#1]

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

[lironbaram]

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)

[MayilVahanan]

HI

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

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

[CELAMBARASAN]

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)

[Gysbert_Wassenaar · Accepted solution]

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.

[#6]

thankyou

[#7]

worked great thanks