How to use logical expressions in QlikSense Scripts ? # New to Qlik Sense

[#1]

Hi All,

I have just started using Qliksense for the first time. Need your suggestions and advice in solving this problem.

I have following information in two separate sheets(excel).

• Headcount of each month (Sample Format)

  Employee ID	Month	Department Code	Location	Department Name
  Ocwen123	January	621	Pune	XXC
  Ocwen124	January	621	Pune	XXC
  Ocwen125	January	621	Pune	XXC
  Ocwen126	February	621	Pune	XXC
  Ocwen123	February	621	Pune	XXC
  Ocwen125	February	621	Pune	XXC
  Ocwen127	February	621	Pune	XXC

• Attrition Tracker for each month(Sample Format)

Employee ID	Month	Department	location	Department Name	Reason for Exit
Ocwen 111	January	621	Pune	XXC	Personal
Ocwen 112	January	621	Pune	XXC	Personal
Ocwen 124	Feb	621	Pune	XXC	Personal

I have to create a measure for Attrition : calculation format is described below

• Attrition= (Total Attrition)/ (Average Headcount)
  • Attrition in January=( Attrition in January)/( Average headcount i.e. total headcount for the month of January)
    • Attrition in January=(2/3)= 66.67%
  • Attrition in February= Total Attrition in January and February/ Average Headcount( January Headcount+ February Headcount)
    • Attrition in February=(3/3.5)= 85.71 %

It will be of great help.

Regards

Anurag Gupta

[Miguel_Angel_Baeyens]

Hi Anurag,

See attached example with your data.

• First you need data cleansing, since values in fields and field names are not named alike.
• Second I used the Master Items library to get what you want, but you could have done it in only one expression. I wanted to show the step by step.
• Third and most important: the way I achieved this is the same you have done with QlikView:

Attrition Acum.
RangeSum(Above(Count({< Source = {'Attrition'} >} [Employee ID]), 0, NoOfRows()))

HeadCount Acum.
RangeAvg(Above(Count({< Source = {'Headcount'} >} [Employee ID]), 0, NoOfRows()))

Calculation
[Attrition Acum.] / [HeadCount Acum.]

Miguel

P.S.: Good way to test app sharing without need of license. Nice! Isn't it? Remember that you have to copy it in C:\Users\<user>\Documents\Qlik\Sense\Apps

[#3]

Hi Miguel,

Thanks a ton for all your help. I again tried the same logic as expalined by you.I have few more questions on the same.

• Since Headcount and Attrition Table has common heading -Employee ID, I am getting this error of "Synthetic Key".  As Employee workforce data for headcount and attrition will have many common heading  ,How should  i avoid it ?
• You have created a new field "Source" ?  What is the purpose of this field ? How have you done that ?

Thanks again for all your help.

[Miguel_Angel_Baeyens]

You are getting a synthetic key because you are just loading, without cleansing, two tables that share field names in more than two occasions. Synthetic keys mean that Qlik finds more than one field named alike in two or more tables and does not know how to solve it.

So the fastest way for me was to create a fact table, but to be able to distinguish between those IDs coming from Attrition and those coming from Headcount, to be able to use them in the expressions. You can do that when facts coming from different sources is just concatenating and having a field that allows to to know which row comes from where.

That's why my expressions have a set analysis in them. And that's why my model does not have a synthetic key.

Another proper, cleaner way to do that is renaming fields in the load editor.

Miguel

[#5]

Hi Miguel,

Thanks for your suggestion. I have one more query ?

Suppose if i need to calculate count of 'January ' and 'February' in the above table. ? How should i do it.

I have used this expression which is giving error.

Count({<Month={'February'}>}

Thanks

Anurag Gupta

[#6]

Field name missing in your expression

Expression should be

Count({<Month={'February'}>[Employee ID]}

Please check the link to get clear idea on Set Analysis

Romancing with Set Analysis

or

http://help.qlik.com/sense/en-US/online/index.html#../Subsystems/Qlik_Sense_Desktop_help/Content/Cha... Analysis

[Miguel_Angel_Baeyens]

You mean adding that as a condition?

Use the following code to use more than one filter in set analysis:

RangeAvg(Above(Count({< Source = {'Headcount'}, Month = {'February'} >} [Employee ID]), 0, NoOfRows()))

Miguel

[josecardenas]

Hi Miguel,

Good post!

This solution is almost I need it.

I have to place the variables as Attr.Acum, HC.Acum and Calculation of your example, based on a variable stored in a external file as excel or a database table.

Is there any approach as was used on Qlikview before.  I've not found an option on Qlik sense as I don't know how to mix a variable from script with the measure on the dashboard app.

The post below is a good example of how was solved in qlikview:

http://www.qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/

Jose.

[josecardenas]

Hi Miguel

I think I've found a solution in this post

http://community.qlik.com/thread/128860

Jose