topic Re: Dynamic What-If Analysis using variables in QlikView

Dynamic What-If Analysis using variables

phcaptjim — Fri, 27 Sep 2013 17:28:24 GMT

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!

Re: Dynamic What-If Analysis using variables

swuehl — Fri, 27 Sep 2013 17:38:55 GMT

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).

Re: Dynamic What-If Analysis using variables

phcaptjim — Fri, 27 Sep 2013 17:49:30 GMT

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.

Re: Re: Dynamic What-If Analysis using variables

swuehl — Mon, 30 Sep 2013 10:16:40 GMT

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.