Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arvindjha2050
New Contributor II

Trend Analysis

Hello Team,

What is the best possible way to calculate trend for the following :

Dimension Drill Down : Year,Quarter,Month

Measure : Attrition Rate

Attrition Rate = sum of people leaving in current quarter / total headcount in the previous quarter

The same applies for year and month as well.

Kindly provide assistance.

Regards,

Arvind

Tags (1)
3 Replies
arvindjha2050
New Contributor II

Re: Trend Analysis

I am using the below formula :

sum(Employee)/Above(count(Employee))

The problem is with above it gives blank value for the first record in drilldown due to above function.

I have attached my qvf for reference.

When i do drill down on Year , one quarter returns null value even though it is not.

On further drilldown one month returns null value.

How to avoid null values?

Kindly help on the same.

Thanks,

Arvind

MVP
MVP

Re: Trend Analysis

You can't sum the field Employee - I am sure that is not a numeric. How do you identify the people that have left? If you have or create some sort of flag to indicate the leavers, then you could say something like:

     =Count({<Leaver = {1}>} Distinct Employee) / Above(Count(Distinct Employee))

arvindjha2050
New Contributor II

Re: Trend Analysis

Hello Jonathan,

Thanks for the same.I mistakenly wrote sum.

I had used formula similar to above but the concern it shows null values.

Dimension - DrillDown : Year ,Quarter ,Month

Measure : Attrition Calculation

It returns correct values but null for one of the columns.Say i have data for all months from 2016 to 2018 then null data for 2015 is acceptable as above function will not find any value.

But even for 2017 when i drill down one of the quarters show null value and month also shows null value for one column.

How can it be fixed?

Thanks in advance. !