Help writing a sort function in an expression

[#1]

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

[#2]

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

[#3]

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

[#4]

Hi alec,

consider loading the excel data using cross-table statement.

Yair.

[jerem1234]

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.