Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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 IDMonth Department CodeLocationDepartment Name
    Ocwen123January 621PuneXXC
    Ocwen124January621PuneXXC
    Ocwen125January621PuneXXC
    Ocwen126February 621PuneXXC
    Ocwen123February 621PuneXXC
    Ocwen125February621PuneXXC
    Ocwen127February621PuneXXC
  • Attrition Tracker for each month(Sample Format)
Employee IDMonthDepartment locationDepartment NameReason for Exit
Ocwen 111January621PuneXXCPersonal
Ocwen 112January621PuneXXCPersonal
Ocwen 124Feb621PuneXXCPersonal

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

8 Replies
MVP
MVP

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

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

Not applicable

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

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.

MVP
MVP

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

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

Not applicable

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

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

Not applicable

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

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

MVP
MVP

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

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
New Contributor III

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

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
New Contributor III

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

Hi Miguel

I think I've found a solution in this post

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

Jose