4 Replies Latest reply: Sep 26, 2017 9:26 AM by Paula van der Velde

# Hi, is there anybody who could tel me what kind of function I should use in QlikSense to get a result similar to the below expression in Excel?

=IF(ISNA(INDEX(\$B:\$K,MATCH(\$C19555,B:B,0),10)),IF(\$K19555=0,"Perfect",IF(\$K19555>0,"New")),IF(INDEX(\$B:\$K,MATCH(\$C19555,B:B,0),10)=0,IF(\$K19555>0,"New",IF(\$K19555=0,"Perfect")),IF(INDEX(\$B:\$K,MATCH(\$C19555,B:B,0),10)>0,IF(\$K19555>0,"Existing",IF(\$K19555=0,"Solved")))))

• ###### Re: Hi, is there anybody who could tel me what kind of function I should use in QlikSense to get a result similar to the below expression in Excel?

Hi Paula,

Based on your excel equation, am I correct in assuming that you are searching for values in a matrix, and returning an indicator based on the cell's value at the cross section of a row and column?

If so, I would recommend taking a look at the data you are loading into QlikSense. In excel, you are able to utilize the cross section of a column and a row, whereas QlikSense is a columnar database. This means that your excel equation does not have a direct comparable in QlikSense.

Without seeing your data, I would not know how to properly adjust it to better work inside QlikSense.

I hope this helps! Let me know if you have any questions.

• ###### Re: Hi, is there anybody who could tel me what kind of function I should use in QlikSense to get a result similar to the below expression in Excel?

Hi Austin,

Thank you for answering my question. I added some data. Perhaps you could have a look to see if it is possible in Qlik Sense or else a suggestion how to build this.

Up to know I build this in a pivot table.

The formula is in column N. There is more into this formula but I think this would be clear to you.

• ###### Re: Hi, is there anybody who could tel me what kind of function I should use in QlikSense to get a result similar to the below expression in Excel?

Hi Paula,

My personal recommendation would be to upload the excel sheet exactly as you have sent it to me. Your column N in the excel file will upload as plain text, and you will be able to achieve your analysis with 0 equations in QlikSense.

Create a data connection to this excel document in the data load editor of QlikSense, and as you update this excel sheet, your visualizations will update upon a "reload" of your data in QlikSense.

Some simple equations that may be useful once your data is in Qlik could be calculating KPIs for each of the statuses in your column N.

for example, count all of the "Perfect" status:

count({\$<[ScoreComparedToLastMonth]={'Perfect'}>}[ScoreComparedToLastMonth])

Let me know if this is helpful or correct!

Thanks again,

Austin

• ###### Re: Hi, is there anybody who could tel me what kind of function I should use in QlikSense to get a result similar to the below expression in Excel?

Hi Austin,

Thank you for looking into my data. The thing is that de data up to column L is coming from our datamart in QlikSense and is already part of our dashboards. We export it to excel to perform the actions in column M and N.

The actions in column M and N I also would like to perform in Qlik Sense itself.