Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Please go through this attachment.
Thanks,
AS
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.
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.
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.
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
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.