topic Change values on excel charts using and excel variable in QlikView

Change values on excel charts using and excel variable

Wed, 28 Mar 2012 10:37:15 GMT

I have an excel file with a chart. Some of the values of such chart are linked through quite complex excel formulas to a variable which is in the same excel file but our of the chart. I would like to make a "What if Analysis" in Qlikview obtaining different values for the data of the chart as I give different values to the mentioned variable.

Is there a guide I can follow to do such thing?

Thanks,

Juan

Change values on excel charts using and excel variable

mazacini — Wed, 28 Mar 2012 15:22:05 GMT

Is this what you need?

1. You will need to create a Variable in Qlikview

2. You will need to build the chart in Qlikview, replicating the Excel formulas in the Qlikview expressions, and referencing the Variable created as Step 1 (ie this variable will replace the Excel Cell Address in the Qlikview expression)

3. Create an Input Box, displaying the Variable created in Step1

The input box will allow you to select values for the variable which will be used by the expression to calculate the chart values.

Rgds

Joe

Change values on excel charts using and excel variable

Thu, 29 Mar 2012 09:25:56 GMT

Joe,

Thanks for your reply. I understand and you are right: that's what I need. Problem is that my degree of sofistication coding Qlikview formulas is very low and I was hoping there was a way around that. I realize there isn't.

My chart is somehow complex with formulas likes this: "=(SI.ERROR(BUSCARV(B4;'Tabla 1'!$B$16:$C$18;2;FALSO);0)+(1-'Tabla 1'!$C$19)*P4)*'Tabla 1'!$F$13". No clue about how to do that on qulikview.

Would be usefull to have a full guide of paralel analysis excel-qulikview.

Thanks again for your help.

Juan

Change values on excel charts using and excel variable

christophebrault — Thu, 29 Mar 2012 14:40:31 GMT

Hi,

Qlikview really does'nt work like an excel file. Don't try to replicate excel formulas, but try to get the same result with qlikview method.

To help you, can you give us some example of your data and the result you need ?

Change values on excel charts using and excel variable

mazacini — Thu, 29 Mar 2012 14:44:46 GMT

Hi Juan

First thing - I am not an expert Qlikview user!

As an accountant, my background was in Excel before I started using Qlikview.

A good understanding of Excel helps, but you do need to change your thinking to really understand Qlikview.

This is a challenging (but enjoyable) part of the process.

How to replace the Lookup (Buscar) function was one of the things I had difficulty understanding.

Here is what I would do.

1. Table1: Load your table (the one with cell B4).

2. Table2: Load the Lookup table B16:B19 (make sure Column B has same name as Column B in Table1)

There is now an association between Table1 and Table2.

If you build a chart with Table1.ColB as a dimension, and introduce Table2.ColC into an expression, it will compute the value of Table2.ColC associated with the ColB value.

You can also use Left Join:

Left Join Table 2 to Table 1 - this will populate Table 1 with a new column containing the value in Column C of Table 2 associated with the value of colun B

The other elements of the formula are variables.

If variable is a constant n and applies to each value of ColB, just add a line to your script:

n as C19

This will create a field C19 for each line of data, with value n (equivalent in Excel to column named C19 with value n in each row).

C19 will then appear as a Dimension for inclusion in expressions.

Otherwise create variable using Create Input Box to allow for user entry of different values for the variable.

Hope this helps!

Joe

Change values on excel charts using and excel variable

Thu, 29 Mar 2012 17:20:39 GMT

Thanks a lot. That's great help. I'll try to make it happen.

Regards,

Juan

Re: Change values on excel charts using and excel variable

Thu, 29 Mar 2012 17:35:57 GMT

OK. Here goes a simplified version of what I need. I have an amount, say, 30.000, that I have to split among a group of guys. These guys are either Managers or Senior Managers; the criteria to split are these:

- They have to be Senior Managers; if the are just Managers, the get no split

- Both Managers and Senior Mangers have shares on the Company

- Senior Managers split the 30K accoring to the shares they own as a percentage on the total amount of shares owned by the Senior Managers

See the chart as an example:

Name	Status	Shares on Co.	% on Co.	% over total Senior Manager Shares	Amount Split
Joe 1	Manager	23	3,17%	0,00%	0
Joe 2	Senior Manager	34	4,69%	8,72%	2.615
Joe 3	Manager	45	6,21%	0,00%	0
Joe 4	Senior Manager	56	7,72%	14,36%	4.308
Joe 5	Manager	67	9,24%	0,00%	0
Joe 6	Senior Manager	78	10,76%	20,00%	6.000
Joe 7	Manager	89	12,28%	0,00%	0
Joe 8	Senior Manager	100	13,79%	25,64%	7.692
Joe 9	Manager	111	15,31%	0,00%	0
Joe 10	Senior Manager	122	16,83%	31,28%	9.385

I would need help to calculate on qlikview the three columns on the left ussing different values for the amount to be split (30K in the example).

Again, thanks for your time and help.

Juan

Change values on excel charts using and excel variable

mazacini — Mon, 02 Apr 2012 10:43:53 GMT

Hi Juan

Here is one way to do it

1. Load Columns 1 to 3 from your file:

LOAD Name,
Status,
SharesFROM

(ooxml, embedded labels, table is Sheet1);

2. Create Inout Box (Title: Enter Amount") adding variable for amount (I called it vAmount)

3. Create a Straight Table chart

Dimensions: Name, Status, Shares

Expressions:

% on Co.

=sum (Shares)/sum(TOTAL (Shares))

% over total Senior Manager Shares

=sum({<Status={[Senior Manager]}>}Shares)/sum ({<Status={[Senior Manager]}>}TOTAL Shares)

Amount Split

=(sum({<Status={[Senior Manager]}>}Shares)/sum ({<Status={[Senior Manager]}>}TOTAL Shares))*vAmount

Result should be as follows:

Change values on excel charts using and excel variable

Mon, 09 Apr 2012 17:38:49 GMT

Mazacini,

Great. I got it working. Thanks a lot.

Best regards,

Juan

Change values on excel charts using and excel variable

mazacini — Fri, 13 Apr 2012 11:17:42 GMT

That's great. Delighted to be of assistance.