Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to create a dashboard which takes quantities from a SQL file, divides them by quantities in an Excel file, and will display the percentages accordingly.
In the SQL file, the quantities are divided up by a column with a certain code. If this code matches the code in the Excel file, it should divide them.
For example, lets say the first red column shown below has a code of '1', the yellow has a code of '2', green of '3'. etc. I want the expression within this chart for the red column to display only the SQL data which has a matching code of '1', the yellow column to
display only the SQL data that has a code of '2', the green for '3', etc. Can someone please help me correctly write out these expression statements? So far, I have =Sum(QuantityInSQL)/Sum (QuantityInExcel) , but I need to make it something like =While(Code = '1', Sum(QuantityInSQL)/Sum (QuantityInExcel)) for the red column, =While(Code = '2', Sum(QuantityInSQL)/Sum (QuantityInExcel)) for the yellow column, etc.
Obviously the While() statement doesn't work in Qlikview, and I can't figure out how to do it in a way that Qlikview will understand.
Any help is greatly appreciated.
Thank you,
Alec
Hi Jeremiah,
I apologize for my late reply, I have been out of the office for the last several days.
I want it to show no matter what the selections are. It will always be populated with data featuring different codes, and I need it to display each code as a separate bar on the bar graph. I believe your solution above will be helpful, I am going to try it and will update with results. In addition, if anything I've said here helps to clarify what I am looking for, please feel free to let me know any ideas you may have.
Thanks,
Alec
Hi Jeremiah,
I have tried your solution and found you are understanding exactly what I am looking for. One small issue though, with the code:
if(only({1}SQLCode) = only({1}ExcelCode), Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))
I want it to say something more like this:
if(only({1}SQLCode) = '1', Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))
The problem I am having is that it is saying there is no data to display when I do it like this. I have also tried:
if(only({1}SQLCode) = '1', Sum(QuantityInSQL)/Sum (QuantityInExcel))
and
if(SQLCode = '1', Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))
and I'm having no luck. Do you know what I can do to make it work?
Thank you so much for your help, I really do appreciate it.
Alec
Hi alec,
consider loading the excel data using cross-table statement.
Yair.
Sorry for getting back so late, but I am not quite sure what you are seeking. For your purpose, I would use the SQL Code as a dimension and then use:
Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel)
as the expression in the bar chart.