topic Re: Convert string to date for comparison in set analysis in QlikView

Convert string to date for comparison in set analysis

Mon, 22 Jul 2013 06:29:37 GMT

Hi,

I have the following data:

Month Amount

01/01/13 200

01/02/13 100

01/03/13 300

01/04/13 300

01/05/13 200

01/06/13 100

01/07/13 500

01/08/13 100

01/09/13 200

01/10/13 100

01/11/13 200

01/12/13 400

The "Month" is a string value. Also I have created a variable, 'vChoosenDate' as '01/04/13' (String too).

I have to display a text object which display the spend to date values.

Example total amount will be 600 if user choosen '01/04/13' (which is April month).

How do I create the analysis code?

I have tried the following but it does not work.

1. Created a temp variable, vTempMonth, as Date#(Month,'DD/MM/YY')

2. Created another temp variable, vTempChoosenDate as Date#(vChoosenDate, 'DD/MM/YY')

3. In my text object, display as =Sum({<vTempMonth={vTempChoosenDate}>}Amount)

Re: Convert string to date for comparison in set analysis

er_mohit — Mon, 22 Jul 2013 06:35:58 GMT

try to create a date field like this in script side

date(date#(Month,'DD/MM/YY'),'DD/MM/YY') AS Date

and in text object write this

=Sum({<Date={'01/04/13'}>}Amount)

Re: Convert string to date for comparison in set analysis

Mon, 22 Jul 2013 06:44:10 GMT

I don't think i can create the date field in script side.

I'm creating as a crosstable.

CrossTable([Chart Month], [Opex Value], 4)

LOAD BUD_UNBUD as [Budgeted Unbudgeted],

TO_FUNCTIONAL_UNIT as [T&O Functional Unit],

PRJ_STATUS as [Project Status],

INIT_CAT as [Initiative Category],

0 as [ ],

OPEX_JAN_CY as '01/01/$(vYear)',

OPEX_FEB_CY as '01/02/$(vYear)',

OPEX_MAR_CY as '01/03/$(vYear)',

OPEX_APR_CY as '01/04/$(vYear)',

OPEX_MAY_CY as '01/05/$(vYear)',

OPEX_JUN_CY as '01/06/$(vYear)',

OPEX_JUL_CY as '01/07/$(vYear)',

OPEX_AUG_CY as '01/08/$(vYear)',

OPEX_SEP_CY as '01/09/$(vYear)',

OPEX_OCT_CY as '01/10/$(vYear)',

OPEX_NOV_CY as '01/11/$(vYear)',

OPEX_DEC_CY as '01/12/$(vYear)'

Resident MIR;

Re: Convert string to date for comparison in set analysis

tresB — Mon, 22 Jul 2013 06:46:12 GMT

Best way is to load the date field as date it in the script using Date(Date#()) , otherwise you can try the following:

Sum( {<Month={ $(vChoosenDate) }>} Amount), temp variables would not be required.

Re: Convert string to date for comparison in set analysis

ariel_klien — Mon, 22 Jul 2013 06:53:16 GMT

I agree with Tres

Do this in the script:

Temp:

LOAD * INLINE [

Month, Amount

01/01/13, 200

01/02/13, 100

01/03/13, 300

01/04/13, 300

01/05/13, 200

01/06/13, 100

01/07/13, 500

01/08/13, 100

01/09/13, 200

01/10/13, 100

01/11/13, 200

01/12/13, 400

];

table:

LOAD *,

Date(Month,'DD/MM/YY') as Month_date

Resident Temp;

drop Table Temp;

Then put your variable vTempChoosenDate and in the text box put: =sum({<Month_date={'<$(vTempChoosenDate)'}>}Amount)

Hope this will help you.

Ariel

Re: Convert string to date for comparison in set analysis

Mon, 22 Jul 2013 09:13:08 GMT

Hi,

I tried the following statement, it keeps return '-' as my result.

=sum({<Month_date={'<$(vTempChoosenDate)'}>}Amount)

But if i hardcode the choosen date, it returns expected result.

=sum({<Month_date={'<01/03/13'}>}Amount)

Re: Convert string to date for comparison in set analysis

tresB — Mon, 22 Jul 2013 09:18:20 GMT

try with vChoosenDate (if you are using the variable like you mentioned earlier, ie. as string) :

=sum({<Month_date={'<$(vChoosenDate)'}>}Amount)

Re: Convert string to date for comparison in set analysis

Mon, 22 Jul 2013 09:29:30 GMT

I have changed my vChoosenDate to

Date(Date#('01/'& vMonth & '/' & vYear, 'DD/MMM/YY'),'DD/MM/YY')

Is this affecting the analysis result?