1 Reply Latest reply: May 28, 2018 2:22 AM by Arvind Kumar Jha

# 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

• ###### 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

• ###### 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))

• ###### 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?