topic What if...? in QlikView

What if...?

Fri, 16 Jul 2010 09:00:23 GMT

Hi all

Im working on a qlikview model but a customer asked me to create a what if analysis using a slider object and a table containing x,y,z

any ideas how to go about doing that?

What if...?

Fri, 16 Jul 2010 09:14:35 GMT

Hi ,

Plz give details of what this table is all about, criteria, table content etc.

Happy Thoughts

What if...?

Fri, 16 Jul 2010 09:23:45 GMT

alright....i got a list of Customers alright. They all got sales, cost, gross profit and gross profit %.

Now i need the create several or if possible just one slider object that depicts certain criteria. e.g

If the sales increase by 10% but the costs increase by 7% what will the outcome be.(How will the GP and GP% change)

The straight table needs to be used to give the outcome by each customer.

let me know if you understand.

I cnt seem to get it right.

What if...?

Fri, 16 Jul 2010 09:44:47 GMT

Hi Blaze,

First thing to do is to create 2 sliders, one for Sales and one for Cost. Then, assign a variable for both like v_Sales for sales slider and v_Cost for cost slider. Next, assign a Min Value, Max Value and Static Step for your slider. Try to use -10 for Min Value, 30 for Max Value and 1 for Static Step. In this case, your slider would be from -10 (negative ten) to 30 with a step of 1. After doing that, create a straight table with a Customer dimension and expressions for sales, cost, gross profit and gp%.

Now create an expression for your "What-If Sales" the formula would be for example:

sum (SALES) * (1 + v_Sales/100)

And for "What-If Cost:

sum (COST) * (1 + v_Cost/100)

And for Gross Profit (that is Sales less the Cost, right?.. then):

sum (SALES) * (1 + v_Sales/100) - sum (COST) * (1 + v_Cost/100)

so therefore, GP% is:

sum (SALES) * (1 + v_Sales/100) / sum (COST) * (1 + v_Cost/100)

- change the number format to percentage..

And TADA!! Now you got your What-If analysis..

Hope that help you solve your problem..

-Lester

What if...?

Fri, 16 Jul 2010 10:03:09 GMT

well it does work, but now the rest of my data is messed up lol all f my graphs say theres no data etc.

Whats happening?

What if...?

Fri, 16 Jul 2010 10:09:43 GMT

hmmm.. that's weird.. coz we didn't change any of your data..

What if...?

Fri, 16 Jul 2010 10:13:19 GMT

i know... but half the data gone even after i delete the what if

What if...?

Fri, 16 Jul 2010 10:23:58 GMT

actually, the "what-if' has nothing to do with your other graphs or charts.. try to check your script, you might have accidentally changed your script and reloaded it..

- Lester

What if...?

Fri, 16 Jul 2010 10:25:58 GMT

if i insert the what if analysis then nothing changes okay, but once i move the slider than all my data on every sheet changes and i cnt reset it using clear or anything. my script seems fine i haven't touched it

What if...?

Fri, 16 Jul 2010 10:54:49 GMT

any Ideas?

What if...?

Fri, 16 Jul 2010 11:43:15 GMT

no updates?

What if...?

Fri, 16 Jul 2010 12:37:10 GMT

this is what happens. In the slider, i set it to 0 and everything goes like this....

As appose from before i use the slider like this... .

What if...?

Mon, 19 Jul 2010 04:19:51 GMT

Hi Blaze,

Seems like there's something wrong on your formula. Try to check the formula for What-if Sales. Based from the image that you attached, the Sales value becomes 0 when the slider is in "0". You might forgot to put "1" on the formula:

sum (SALES) * ( 1 + v_Sales/100)

- Lester

What if...?

Mon, 19 Jul 2010 07:38:13 GMT

no there s a 1 in the formula buddy . Any other suggestions?

If i dont move the slider then nothing changes but as soon as i move it, then the whole document goes bonkerz

What if...?

Mon, 19 Jul 2010 08:14:46 GMT

Hmmm.. can you post a screen shot of your slider's properties and a charts expression tab? If possible..

What if...?

suniljain — Mon, 19 Jul 2010 08:20:05 GMT

Pls Check attached Doc.

It will Help You.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8182.What-If-analysis.doc:550:0]

What if...?

Mon, 19 Jul 2010 09:00:19 GMT

Hi,

please check the slider, did you connect to Variable with single mode?

What if...?

Mon, 19 Jul 2010 10:05:59 GMT

Yea i did but its still not working

What if...?

Mon, 19 Jul 2010 10:20:37 GMT

Worked like a charm. Thanks so much

What if...?

Tue, 20 Jul 2010 10:38:24 GMT

i got the whole thing working but now i need to add a slider contaning quantity. Ive put in the slider and it works and ive put it in the table along with sales, gp etc but whenever i move the quantity slider, the gp result and the cost does not change. there is a problem with the formulas but i cnt figure it out.

Formulas are:

sum(Cost) * (1 + vCost2/100) for What if Cost

and sum (Sales) * (1 + vSales2/100) - sum (Cost) * (1 + vCost2/100) for What if GP

Any help?

Thanks in advance