Using Variable as a Dimension

[#1]

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.

[sunny_talwar]

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

[#3]

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.

[sunny_talwar]

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

[johnw · Accepted solution]

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

[#6]

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?

[trdandamudi]

May be as below:

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

[sunny_talwar]

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] ) +
              )

[#9]

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

[#10]

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