Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
 
					
				
		
 rwunderlich
		
			rwunderlich
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Greetings set analysis gurus. Consider the following table:
Dept, Account, Period, Value
D1, A1, 1, 100
D2, A2, 1, 200
D2, A2, 2, 300
D2, A3, 2, 350
D3, A2, 2, 400
D3, A3, 2, 500
D3, A4, 2, 600
I select Period 1 and Dept D2 as my current selections. I want to display the Values from the selected period (1) and the next period (2). I want the chart to only show accounts that are linked to the selections -- A2 & A3 in this case. Using the following for the Period 2 expression:
=sum( {1<Period = {$(#=Only(Period)+1)}>} Value )
I get Account A4, which is not part of the selection.
| Account | Period 1 | Period 2 | 
|---|---|---|
| A2 | 200 | 700 | 
| A3 | 0 | 850 | 
| A4 | 0 | 600 | 
I can modify the expression to include the Dept, but this only works when I select a single Dept.
=sum( {1<Period = {$(#=Only(Period)+1)}, Dept = {$(=Only(Dept))}>} Value )
| Account | Period 1 | Period 2 | 
|---|---|---|
| A2 | 200 | 300 | 
| A3 | 0 | 350 | 
What expression would I use for Period 2 if I to filter with multiple selected Dept, for example, both D1 & D2 selected. In that case I would expect to see accounts A1 & A2 with correct values.
An example qvvw is attached.
Thanks,
-Rob
 
					
				
		
Hi Rob,
Maybe my solution works for you.
I think there would be a possible optimization in the variable calculation, but I'm not sure:
=if(getexcludedcount(Dept) > 0,concat('"' & Dept & '"',','))
Regards.
 
					
				
		
Hi Rob,
Maybe my solution works for you.
I think there would be a possible optimization in the variable calculation, but I'm not sure:
=if(getexcludedcount(Dept) > 0,concat('"' & Dept & '"',','))
Regards.
 
					
				
		
 jonathandienst
		
			jonathandienst
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Rob
You got A4 from your first expression because of the {1< set selector which overrides all selections (including your Department selection).
If I've understood you correctly, Just remove the 1, so the expression reads:
=sum({<Period = {$(#=Only(Period)+1)}>} Value)
Jonathan
 
					
				
		
 rwunderlich
		
			rwunderlich
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Thanks Miguel! Your solution works great.
-Rob
 
					
				
		
 rwunderlich
		
			rwunderlich
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		
Jonathan Dienst wrote:If I've understood you correctly, Just remove the 1, so the expression reads:
I need the 1 selector to get data from the other period -- which is not selected.
-Rob
 
					
				
		
Hi Rob,
Jonathans suggestion will work. You wont need the {1} for the second period as period is a dimension in the chart. I have just tried it and it looks correct.
Kind Regards,
Footsie
 
					
				
		
 rwunderlich
		
			rwunderlich
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		I'm not seeing how it would work without the "1" selector. Can you post a working qvw based on my original example?
Thanks,
Rob
 
					
				
		
 johnw
		
			johnw
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		
Rob Wunderlich wrote:I'm not seeing how it would work without the "1" selector. Can you post a working qvw based on my original example?
I'm a bit confused about the final result that you want, but since you verified Miguel's answer, I guess you want the table that he shows when no departments are selected? If so, this returns the same answer using only set analysis and no variables:
sum({<Dept=P(),Period={$(=Only(Period)+1)}>} Value)
Dept=P() says to "select" only the possible departments based on the other selections. So you'll only get period 1 departments, D1 and D2. Then we override the Period selection, adding 1 to it. The "1" set shouldn't be used as we don't want to override ALL selections, only the period selection. See attached. Hopefully I've understood what you're looking for.
 
					
				
		
 rwunderlich
		
			rwunderlich
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Thanks John. That's a cleaner solution. I like it and it gives me the result I'm after.
-Rob
