topic Re: Scenario Analysis with Alternate States in QlikView

Scenario Analysis with Alternate States

Tue, 31 May 2016 23:25:15 GMT

All - I am looking to to compare alternative states and use a slider to shift # of Hours from company A to company B to generate $ savings. Essentially it would be:

# of Hours(Firm A) * vVar (slider) * (Firm A Rate - Firm B Rate)

Correct?

Right now I have the following equation(s):

State 1 - [Current Firm]

State 2 - [New Firm]

# of hours Firm A = num(sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"}, Position -= {"=NULL()"}>} [Submitted Hours]),'#,##0')

Avg Rate $ Firm A -

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours]),'$#,##0'

Avg Rate $ Firm B -

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours]),'$#,##0'

Any idea how to put this all into on equation where a slider (vVar) will effect the amount saved from shifting hours from firm A to firm B

Re: Scenario Analysis with Alternate States

sunny_talwar — Tue, 31 May 2016 23:37:46 GMT

Not sure I completely understand your requirement. Would you be able to elaborate a little more?

Re: Scenario Analysis with Alternate States

Tue, 31 May 2016 23:43:02 GMT

Scenario -

Select a firm in State 1:

Name:Firm A

Hours: 1,502

Avg Rate: $365

Select a firm in State 2:

Name: Firm B

Hours: 500

Avg Rate: $225

Use slider to shift to 500 hours @ $365 rate to Firm B where the rate is $225. What's your total savings?

Difference of $365 - $225 *500 hours = savings

Re: Scenario Analysis with Alternate States

swuehl — Tue, 31 May 2016 23:53:16 GMT

Seems like you already have your parts of the expression working on their own, right? What's the exact issue when combining the expressions, then? Have you tried:

(

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours])

)

(

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours])

)

* vVar

Re: Scenario Analysis with Alternate States

Wed, 01 Jun 2016 00:02:33 GMT

The expressions with Sum({[Current Firm}]), Sum({[New Firm]} seem to not be working on their own as well..

Re: Scenario Analysis with Alternate States

swuehl — Wed, 01 Jun 2016 00:07:28 GMT

Well, then I would suggest to fix these expressions first.

Do the expressions work in default state when selecting a firm?

I think this set modifier will not work as expected, since NULL() is not a values, hence can't be removed assigned list of values of field Position:

,Position -= {"=NULL()"}

You might want to look into

Excluding values in Set Analysis

edit:

Or maybe try

,Position = {"*"}

instead.

Re: Scenario Analysis with Alternate States

Wed, 01 Jun 2016 00:12:17 GMT

They do not work in the default state when a firm is selected, only their corresponding state within the set analysis. Looking into it now, but is the syntax correct for the alternate state's in the set analysis e.g.

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours]),'$#,##0'

Re: Scenario Analysis with Alternate States

sunny_talwar — Wed, 01 Jun 2016 00:14:08 GMT

May be this:

Sum({[Current Firm]<[Vendor Ty

Re: Scenario Analysis with Alternate States

Wed, 01 Jun 2016 00:17:19 GMT

Ahh yes, that was the issue! Thanks Sunny.