Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to create a variable in the script from another variable.
first i create this ..
LET vCurrentYearFiscalStartDate = '=DATE(FirstDayCurrentFiscalYear)';
Then i want to create another variable using the above variable.
LET
vItemCount_Listings =
'COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">=$(vCurrentYearFiscalStartDate)"}
>}
[ld_Item_Description])';
The above didnt work, so after a bit of research i tried the following which also didnt work.
LET
vItemCount_Listings =
'COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">='&chr(36)&'(=vCurrentYearFiscalStartDate)"}
>}
[ld_Item_Description])';
Does anyone have any ideas please?
Just match the format of your initial variable with the field format, maybe like
LET vCurrentYearFiscalStartDate = '=Timestamp(FirstDayCurrentFiscalYear,'DD/MM/YYYY hh:mm:ss')';
Maybe like this?
LET
vItemCount_Listings =
'COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">='&chr(36)&'($(vCurrentYearFiscalStartDate))"}
>}
[ld_Item_Description])';
Hi Stefan,
that brings back a zero value.
If i change it to use a date instead of variable then i get a value of 139.
LET
vItemCount_Listings =
'COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">=01/07/2012 00:00:00"}
>}
[ld_Item_Description])';
But i want this to work using a variable instead of hardcoding the date/
If I run the script, I get this variable definition back:
COUNT({$<
[it_target_type]={"Listings"},
[ld_Dist]={"4"},
[ld_Outlet_Check_Date]={">=$(=DATE(FirstDayCurrentFiscalYear))"}
>}
[ld_Item_Description])
I think this is what you intend to get back, right?
If you say, you get a value of 139 back (resp. zero), I assume you mean you get this returned when using the variable in an e.g. text box expression, right?
I can imagine some issues with the set modifier format, 01/07/2012 00:00:00 is a timestamp format, while your standard DATE format is probably missing the time string.
Hi Stefan,
Yes you arfe exactly right. I am getting the 139 in a text box using the variable name. what would be the easiest way of getting around the timestamp issues. we dont have the option to remove the time as this is needed.
Just match the format of your initial variable with the field format, maybe like
LET vCurrentYearFiscalStartDate = '=Timestamp(FirstDayCurrentFiscalYear,'DD/MM/YYYY hh:mm:ss')';
Hi Stefan,
Thanks
I got it working by just using the Timestamp function. I had script errors when i used the ,'DD/MM/YYYY hh:mm:ss' at the end.
Many thanks for your help
Chris