Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Change values on excel charts using and excel variable

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

1 Solution

Accepted Solutions
mazacini
Not applicable

Change values on excel charts using and excel variable

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:

205755.PNG

9 Replies
mazacini
Not applicable

Change values on excel charts using and excel variable

Hi

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

Not applicable

Change values on excel charts using and excel variable

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

christophebrault
Not applicable

Change values on excel charts using and excel variable

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 ?

mazacini
Not applicable

Change values on excel charts using and excel variable

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

Not applicable

Change values on excel charts using and excel variable

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

Regards,

Juan

Not applicable

Re: Change values on excel charts using and excel variable

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:

NameStatusShares on Co.% on Co.% over total Senior  Manager SharesAmount Split
Joe 1Manager233,17%0,00%0
Joe 2Senior Manager344,69%8,72%2.615
Joe 3Manager456,21%0,00%0
Joe 4Senior Manager567,72%14,36%4.308
Joe 5Manager679,24%0,00%0
Joe 6Senior Manager7810,76%20,00%6.000
Joe 7Manager8912,28%0,00%0
Joe 8Senior Manager10013,79%25,64%7.692
Joe 9Manager11115,31%0,00%0
Joe 10Senior Manager12216,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

mazacini
Not applicable

Change values on excel charts using and excel variable

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:

205755.PNG

Not applicable

Change values on excel charts using and excel variable

Mazacini,

Great. I got it working. Thanks a lot.

Best regards,

Juan

mazacini
Not applicable

Change values on excel charts using and excel variable

That's great. Delighted to be of assistance.