Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Would you be able to share a sample to look at the issue?
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.
You can only use field names on the LHS of the set modifier. Since vRate is a calculation, it won't work here
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
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?
May be as below:
Sum({1 <Region>}aggr($(vRate),Region ))
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] ) +
)
This does it, but the numbers are a little inflated.. from mostly single to triple digits.
Added parenthesis to enclose the numerator expression, works perfect. Thanks!