Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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;
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.
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
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)
try with vChoosenDate (if you are using the variable like you mentioned earlier, ie. as string) :
=sum({<Month_date={'<$(vChoosenDate)'}>}Amount)
I have changed my vChoosenDate to
Date(Date#('01/'& vMonth & '/' & vYear, 'DD/MMM/YY'),'DD/MM/YY')
Is this affecting the analysis result?