Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am trying to calculate the following:
Avg({<[v_Variable1]={'Yes'},[Name]={$(=v_Variable 2)}>} [Weight])
The aim is to calculate the average of everything that fulfills the 'yes' requierement for variable 1.
However I can't seem to get the syntax right, as it is not correctly referencing variable 1.
The result is the same as Avg(Name]={$(=v_Variable 2)}>} [Weight])
Can anyone tell me how to reference variable 1 correctly, so I get a correct result?
Thank you very much in advance!
You couldn't use a variable on the left side of a set analysis condition pair unless it returns a valid fieldname. This means you need to apply this condition within an if-loop or using it as a boolean factor, means something like:
if('$(v_Variable1)' = 'Yes', Avg(Name]={$(=v_Variable 2)}>} [Weight]))
or
Avg(Name]={$(=v_Variable 2)}>} [Weight] * $(v_Variable1))
whereby here v_Variable1 isn't yes or no else 1 or 0.
- Marcus
You couldn't use a variable on the left side of a set analysis condition pair unless it returns a valid fieldname. This means you need to apply this condition within an if-loop or using it as a boolean factor, means something like:
if('$(v_Variable1)' = 'Yes', Avg(Name]={$(=v_Variable 2)}>} [Weight]))
or
Avg(Name]={$(=v_Variable 2)}>} [Weight] * $(v_Variable1))
whereby here v_Variable1 isn't yes or no else 1 or 0.
- Marcus
I'm guessing that you simply need to use Dollar Sign Expansion for your variable to get the value it evolves to.
The great thing I love about Expressions in Qlik Sense is that at the bottom of the screen it shows you exactly what the result will be of your formulas. Notice here I'm using vVariable1 and the expression editor shows me that is exactly what it will result in. But in the second screen shot you will see that I used dollar sign expansion $(vVariable1) instead and voila it shows me the country name that is assigned to the variable.
Thank you Marcus & Dalton, you helped out a lot!
Rearlly do appreciate it.