Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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 |
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
It will be of great help.
Regards
Anurag Gupta
Hi Anurag,
See attached example with your data.
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
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.
Thanks again for all your help.
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
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
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
or
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
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.