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

[#1]

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.

[Miguel_Angel_Baeyens]

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

[amit_saini]

Please go through this attachment.

Thanks,
AS

[#4]

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.

[rubenmarin]

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.

[Peter_Cammaert]

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.

[Peter_Cammaert · Accepted solution]

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

[#8]

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.