Set Analysis - Qualifying global set with current selections

[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

[#2 · Accepted solution]

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]

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]

Thanks Miguel! Your solution works great.

-Rob

[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

[#6]

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]

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]

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]

Thanks John. That's a cleaner solution. I like it and it gives me the result I'm after.

-Rob