topic Re: Using Variable as a Dimension in QlikView

Using Variable as a Dimension

Wed, 25 Nov 2020 16:16:04 GMT

I have a variable/set analysis problem that i have tried to resolve for the last couple of day without much success. I hope this forum could point me to the right direction.

I have a variable,

vRate =(
               (sum(aggr(sum(DISTINCT [RegionA]), [TransID]))*0.21) +
               (sum(aggr(sum(DISTINCT [RegionB]), [TransID]))*0.25 )
               )
               /
               (
               Count({$<Complete= {">0"}>} DISTINCT [TransID] )+
               Count({$<Review= {">0"}>} DISTINCT [TransID] )+
               )

I want to select, and ultimately count stores where $(vRate) <= x% on table 1 and where $(vRate) >=y% on table 2.

Something like Count({<$(vRate)<={y}>}Stores) but its throwing errors at the..{3}>..) mark. What could I be doing wrong?

I also tried to load the variable as a script using both LET and SET vRate =(…);

LOAD $(vRate) AS [Rate] on QVW without success.

Re: Using Variable as a Dimension

sunny_talwar — Fri, 30 Dec 2016 16:39:23 GMT

Would you be able to share a sample to look at the issue?

Re: Using Variable as a Dimension

Fri, 30 Dec 2016 17:59:56 GMT

I can't share a sample, however, the variable, vRate works fine. The expression Count({<$(vRate)<={y}>}Stores) is what is generating the error. I'm not sure if there's a better way to write the expression, or how to effectively make the variable behave as a [field] or dimension.

Re: Using Variable as a Dimension

sunny_talwar — Fri, 30 Dec 2016 18:24:23 GMT

You can only use field names on the LHS of the set modifier. Since vRate is a calculation, it won't work here

Re: Using Variable as a Dimension

johnw — Fri, 30 Dec 2016 19:06:17 GMT

It might be slow, but perhaps you could do it something like this:

-sum(aggr($(vRate)<=.20,Stores))

Or perhaps more clearly though less concise:

count(distinct aggr(if($(vRate)<=.20,Stores),Stores))

Though with only TransID as a dimension in your inner aggrs, I'm not sure that would work. Perhaps if Stores was added there as well

Re: Using Variable as a Dimension

Sat, 31 Dec 2016 03:03:50 GMT

Worked like a charm! I have related question for a bar chart for the same project.. It's using the Region as Dimension and $(vtRate) as an expression. All the bars need to be visible even after a selection, i.e., disable the chart from being dynamic, other than greying out the inactive bars. I used the following expression, Sum({<Region>}aggr($(vRate),Region )) but that doesn't seem to work.. the chart still acts dynamically. Is there a solution around this?

Re: Using Variable as a Dimension

trdandamudi — Sat, 31 Dec 2016 10:46:29 GMT

May be as below:

Sum({1 <Region>}aggr($(vRate),Region ))

Re: Using Variable as a Dimension

sunny_talwar — Sat, 31 Dec 2016 12:44:05 GMT

I think you need to ignore selection in Region within the expression you have for vRate

(Sum({<Region>} Aggr(Sum({<Region>} DISTINCT [RegionA]), [TransID]))*0.21) +
(Sum({<Region>} Aggr(Sum({<Region>} DISTINCT [RegionB]), [TransID]))*0.25)
              )
              /
              (
              Count({$<Complete= {">0"}, Region>} DISTINCT [TransID] ) +
              Count({$<Review= {">0"}, Region>} DISTINCT [TransID] ) +
              )

Re: Using Variable as a Dimension

Sun, 01 Jan 2017 14:56:59 GMT

This does it, but the numbers are a little inflated.. from mostly single to triple digits.

Re: Using Variable as a Dimension

Mon, 02 Jan 2017 06:26:12 GMT

Added parenthesis to enclose the numerator expression, works perfect. Thanks!

Re: Using Variable as a Dimension

sunny_talwar — Mon, 02 Jan 2017 13:23:26 GMT

Awesome