topic Re: Syntax question, regarding how to use a variable inside set analysis in QlikView

Syntax question, regarding how to use a variable inside set analysis

Thu, 12 Mar 2015 10:45:44 GMT

Hi I'm new to Qlikview and have a syntax question, regarding how to use a variable inside set analysis.

I need to use two filters for worked hours, they work separately but not when combined.

This works:

sum({$<[Store Name]-={'Name1','Name2'}>} Worked_Hours)

This works:

sum( $(v_LastWeek_Filter) Worked_Hours)

But when I combine them it does not work:

sum({$<DateKey={$(v_LastWeek_Filter)},[Store Name]-={'Name1','Name2'}>} Worked_Hours)

I have tried different things like putting an equal sign and commas here and there but without luck, can anyone help?

Regards.

Re: Syntax question, regarding how to use a variable inside set analysis

Miguel_Angel_Baeyens — Thu, 12 Mar 2015 11:18:03 GMT

Hi Guðmundur,

It all comes to first how the variable has been SET or LET and second, what the variable actually contains.

If $(v_LastWeek_Filter) equals to 7, a numeric value that matches DateKey, then the syntax is correct.

But my guess is that this variable has a date representation (DD/MM/YYYY) or something similar, therefore you need single quotes to match properly:

Sum({$<DateKey={'$(v_LastWeek_Filter)'},[Store Name]-={'Name1','Name2'}>} Worked_Hours)

Again, it will depend on how are values stored into DateKey and how are they n v_LastWeek_Filter.

Miguel

Re: Syntax question, regarding how to use a variable inside set analysis

amit_saini — Thu, 12 Mar 2015 11:19:59 GMT

Please go through this attachment.

Thanks,
AS

Re: Syntax question, regarding how to use a variable inside set analysis

Thu, 12 Mar 2015 12:27:44 GMT

Hi and thank you for your answer,

all variables are created using LET, and DateKey is a date representation (DD/MM/YYYY). Tried the single quotes but that does not work.

This is what the other variables look like.

v_LastWeek_Filter:

{$<DateKey={"$(='>=' & '$(v_StartOfLastWeek)' & '<=' & '$(v_EndOfLastWeek)')"},Year=,Week=,Day=,Month,fYear=>}

v_StartOfLastWeek:

=WeekStart( Date( v_StartOfWeek- 1))

v_StartOfWeek:

=WeekStart(v_MaxDate)

v_EndOfLastWeek:

=WeekEnd(v_StartOfLastWeek)

Regards.

Re: Syntax question, regarding how to use a variable inside set analysis

rubenmarin — Thu, 12 Mar 2015 12:38:01 GMT

Hi gudmundur,

You can define your v_LastWeek_Filter variable like:

DateKey={"$(='>=' & '$(v_StartOfLastWeek)' & '<=' & '$(v_EndOfLastWeek)')"},Year=,Week=,Day=,Month,fYear=

(removing the {$< from start and the >} from the end

then you can use:

sum({$<$(v_LastWeek_Filter),[Store Name]-={'Name1','Name2'}>} Worked_Hours)

Not tested, the expression dialog will mark some errors but it should work.

Re: Syntax question, regarding how to use a variable inside set analysis

Peter_Cammaert — Thu, 12 Mar 2015 12:42:50 GMT

Always a good technique is to replace $-substitution variables at the highest level with what they actually contain. For example, an expression like

sum({$<DateKey={$(v_LastWeek_Filter)},[Store Name]-={'Name1','Name2'}>} Worked_Hours)

where v_LastWeek_Filter contains:

{$<DateKey={"$(='>=' & '$(v_StartOfLastWeek)' & '<=' & '$(v_EndOfLastWeek)')"},Year=,Week=,Day=,Month,fYear=>}

produces an initital text substitution result of:

sum({$<DateKey={{$<DateKey={"$(='>=' & '$(v_StartOfLastWeek)' & '<=' & '$(v_EndOfLastWeek)')"}, Year=,Week=,Day=,Month,fYear=>}},[Store Name]-={'Name1','Name2'}>} Worked_Hours)

Apparently the problem is that you seem to be nested set analysis... I think QlikView doesn't support nested set analysis except using P() and E() functions.

Re: Syntax question, regarding how to use a variable inside set analysis

Peter_Cammaert — Thu, 12 Mar 2015 12:48:36 GMT

A possible solution:

Put a list of set modifiers in v_LastWeek_filter instead of a complete set spec:

DateKey={"$(='>=' & '$(v_StartOfLastWeek)' & '<=' & '$(v_EndOfLastWeek)')"},Year=,Week=,Day=,Month,fYear=

Rewrite your expression so that it includes these set modifiers in an existing set spec, like in:

sum({$<$(v_LastWeek_Filter),[Store Name]-={'Name1','Name2'}>} Worked_Hours)

The small version that will still work then becomes something like this:

sum({$<$(v_LastWeek_Filter)>} Worked_Hours)

Best,

Peter

Re: Syntax question, regarding how to use a variable inside set analysis

Fri, 13 Mar 2015 16:28:12 GMT

Thank you Peter,

got this to work:

sum({$<DateKey={"$(='>=' & '$(v_StartOfLastWeek)' & '<=' & '$(v_EndOfLastWeek)')"},Year=,Week=,Day=,Month=,fYear=,[Store Name]-={"Name1","Name2"}>}Worked_Hours)

Regards.