Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

help to calculate a expression

Hi need some help

i want to calculate total no of hires :

suppose i select nov and 2017

i get the headcount for november and the attrition for november

total hires will be = headcount for (nov -1) - attrition for nov + x = headcount for nov

i want to find x please help me write it in a form of expression. will be a great help

help req.png

3 Replies
Valued Contributor

Re: help to calculate a expression

Hello Sahil,

you can achieve this by using set analysis (A Primer on Set Analysis). See a very simple example:

Sum(Headcount) //headcount for selected period

Sum({<Month={$(=Max(Month)-1)}>} Headcount) //headcount for previous month

This example does not account for change of year (when you want to calculate your measure for January), so it needs to be expanded, but you should get the idea.



New Contributor

Re: help to calculate a expression

Hi Juraj thanks for the quick reply but still i am not able to get the desired result

i used this expression

count({<[As_On link.autoCalendar.Month]={$(=([As_On link.autoCalendar.Month])-1)}>} distinct EmployeeNo)

but i am not getting the result pls help

help 1.png

Valued Contributor

Re: help to calculate a expression

Hi Sahil,

as I said, my example does not consider change in year, so hence the result. Try something like this:


    [As_On link.autoCalendar.Year]={$(=Year(AddMonths(MakeDate(Max([As_On link.autoCalendar.Year]), Max([As_On link.autoCalendar.Month])), -1)))},

    [As_On link.autoCalendar.Month]={$(=Month(AddMonths(MakeDate(Max([As_On link.autoCalendar.Year]), Max([As_On link.autoCalendar.Month])), -1)))}


distinct EmployeeNo)

Again, this is just an example, although it should work in your case, but you should optimize it either by creating some sort of month sequence in your data (so you do not need to handle year when comparing Jan/Dec data across years), or by creating variables which would calculate correct year and month values for you, or by creating a The As-Of Table.

Hope this helps