Dynamic What-If Analysis using variables

[phcaptjim]

I have a question about dynamic WHAT-IF analysis using variables.  Here is what I am hoping to accomplish.

Using the chart below, I want to say WHAT IF MEAS_1 was always 1, how many providers would be at a total of 3?  I would also want to ask what if MEAS_1 and MEAS_2 were both 1, how many providers would be at a total of 3.  Perhaps have the user select which measures to check. I'm sure there is a way to accomplish this in QVW, I have just not figured it out yet.  Any ideas?

PROV	DATE	MEAS_1	MEAS_2	MEAS_3	TOTAL
A	1/1/2013	1	1	1	3
B	1/1/2013	1	0	0	1
C	1/1/2013	0	0	1	1
D	1/1/2013	0	0	0	0
E	1/1/2013	0	1	1	2

Any help would be greatly appreciated!  Thanks!

[swuehl]

This is quite similar to the question you asked here, right? The solutions provided should work (it might get a bit more tricky if you want to prepare for multiple set measures in a script solution).

Given the suggested solution for a dynamic approach:

=sum(-1 * (MEASURE_2='Y') * (MEASURE_3='Y') * (MEASURE_4='Y') )

You can try creating a variable that creates the string

(MEASURE_2='Y') * (MEASURE_3='Y') * (MEASURE_4='Y')

based on user selecting the Measures to be assumed 'Y' (So all others should be used for the string).

[phcaptjim]

Yes Swuehl.  The static solution works well using the expression you suggested with Sum * -1 to get the answer, my trouble lies with having the user make the selections.  I like your suggestion though and will try and implement.

[swuehl]

It all burns down to creating a field that contains all your Measure field names (e.g. called Measures), then use something like

=sum( concat({1-$} Measures, ' * ') )

See attached sample.