Convert string to date for comparison in set analysis

[#1]

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)

[er_mohit]

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)

[#3]

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;

[tresB]

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.

[ariel_klien]

Hi

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.

BR

Ariel

[#6]

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)

[tresB]

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

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

[#8]

I have changed my vChoosenDate to

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

Is this affecting the analysis result?