Dollar Sign Expansion not working properly

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

I'm very confused. I have a field that contains a list of names of employees that have interacted with an account.

example data:

account analyst

123 Joe

234 Kim

I use a variable to store the names of the team that I work with in a variable expression, and use that to match accounts they've been tied to, using IF(MATCH())

This work fine.

I have an issue when trying to utilize this in an expression. For example, I'm trying to find the average of the accounts they are tied to in any given month.

My formula looks like

((SUM(Var1Dummy)) + (SUM(Var2Dummy))) /

(Count(Distinct($(vTeam))))

The reason I'm confused is because this formula does NOT return the correct number for average. If i multiple it back by the number of people on the team, it's greater than 100% of the actual number for that month.

When I use (Count(Distinct($(vTeam)))) In a Text Object - It returns 14, the number of analysts on the team.

When I hardcode '14' in the formula, it seems to return the appropriate number for the monthly average. I just don't want to have to hardcode the amount of analysts on the team. I would rather it change when the variables is updated with any analyst who has joined/left the team.

I'm confused as to how this SAME formula is resolving in a text object appropriately, 14, but not return the appropriate amount when used in the formula. Also, I've wrapped the numerator and denominator in () to ensure they are resolving correctly.

Re: Dollar Sign Expansion not working properly

marcus_sommer — Wed, 16 Nov 2016 07:37:03 GMT

I think you need to ignore the dimensions of the chart. Try it with: Count(Distinct TOTAL($(vTeam)) or maybe putting these calculations into a variable, too and using them this variable within your expression.

- Marcus

Re: Dollar Sign Expansion not working properly

amirkachlon — Wed, 16 Nov 2016 07:48:32 GMT

In the expression vTeam , add '=' at the beginning.

And the calculation will be calculated globally once and will give you the desired value

topic Re: Dollar Sign Expansion not working properly in QlikView

Dollar Sign Expansion not working properly

Re: Dollar Sign Expansion not working properly

Re: Dollar Sign Expansion not working properly