Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with variable in script

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Just match the format of your initial variable with the field format, maybe like

LET vCurrentYearFiscalStartDate = '=Timestamp(FirstDayCurrentFiscalYear,'DD/MM/YYYY hh:mm:ss')';

View solution in original post

6 Replies
swuehl
MVP
MVP

Maybe like this?

LET

vItemCount_Listings =

'COUNT({$<

[it_target_type]={"Listings"},

[ld_Dist]={"4"},

[ld_Outlet_Check_Date]={">='&chr(36)&'($(vCurrentYearFiscalStartDate))"}

>}

[ld_Item_Description])';

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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/

swuehl
MVP
MVP

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.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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.

swuehl
MVP
MVP

Just match the format of your initial variable with the field format, maybe like

LET vCurrentYearFiscalStartDate = '=Timestamp(FirstDayCurrentFiscalYear,'DD/MM/YYYY hh:mm:ss')';

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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