
Change values on excel charts using and excel variable
Joe Kirwan Mar 28, 2012 11:22 AM (in response to Juan Aisa Blanco)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

Change values on excel charts using and excel variable
Juan Aisa Blanco Mar 29, 2012 5:25 AM (in response to Joe Kirwan)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 excelqulikview.
Thanks again for your help.
Juan

Change values on excel charts using and excel variable
Joe Kirwan Mar 29, 2012 10:44 AM (in response to Juan Aisa Blanco)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
Juan Aisa Blanco Mar 29, 2012 1:20 PM (in response to Joe Kirwan)Thanks a lot. That's great help. I'll try to make it happen.
Regards,
Juan




Change values on excel charts using and excel variable
Christophe Brault Mar 29, 2012 10:40 AM (in response to Juan Aisa Blanco)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 ?

Re: Change values on excel charts using and excel variable
Juan Aisa Blanco Mar 29, 2012 1:35 PM (in response to Christophe Brault)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
Joe Kirwan Apr 2, 2012 6:43 AM (in response to Juan Aisa Blanco)Hi Juan
Here is one way to do it
1. Load Columns 1 to 3 from your file:
LOAD Name,
Status,
SharesFROM
[E:\Work\Work_BI\Qlik\Forums & Learning\Forum 205755.xlsx]
(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
Juan Aisa Blanco Apr 9, 2012 1:38 PM (in response to Joe Kirwan)Mazacini,
Great. I got it working. Thanks a lot.
Best regards,
Juan

Change values on excel charts using and excel variable
Joe Kirwan Apr 13, 2012 7:17 AM (in response to Juan Aisa Blanco)That's great. Delighted to be of assistance.



