Null values using above function in Qlik

[arvindjha2050]

Hello Team,

I have a dataset from Jan 2015 to Mar 2018.

I have used the below function :

Measure : count([Employee #])/above(count([Employee #]))

Dimension Drill Down : Year , Quarter , Month

Year :

The first year does not have value as there is no value above it

On drilldown Year 2017 ,Q4 has null value ,

On drilldown Q1,March has null value.

Ideally Q4-17 should not have null value as it's above function should call Q3-17 and similar for month.

For every selection there is null value for one field value.

How to fix it , is there any alternate mechanism.

Thanks,

Arvind

[ogautier62]

Hi

it seems that first row of each dimension is null :

your sheet is sorted descending order ?

above is null for each first row of each dimension;

to avoid write : above(total count ......

regards

[arvindjha2050]

Hello Olivier,

Yes it's sorted in descending order.

I used the total function but it's still giving null value for one of the dimension values at every stage of drilldown.

Is there any other function?

Thanks,

Arvind

[ogautier62]

OK,

right, I don't see with drilling dimension,

this works if dimensions deployed

[agigliotti]

could you share an app with mock data showing your expeted result?

[arvindjha2050]

Hello Andrea,

I have created dummy dataset for the same.Also attached App Overview section in that i require values to be populated for each of the drilldowns Year,Quarter,Month but every level of drilldown has a null value.

I understand it is because of above function , i just want to know alternate of the same.

Kindly let me know for further understanding and thanks for the time.

Thanks,

Arvind

[arvindjha2050]

Hello Andrea,

Any ways around the same?

Thanks

[agigliotti]

you should use an expression as below:

Count(EmployeeID)

/

below( Count(EmployeeID) )

and sorting numerically descending

in this way only the last dimension value will be null

hope it helps.

[hacr]

What about making an IF() and utilizing the RowNo() funktion?

[arvindjha2050]

Hello Andrea,

This logic doesn't work in the above case because Dimension is a drilldown and every level has a null value resulting in multiple null values.

I will explain with example :

Dimension DrillDown : Year , Quarter , Month

Suppose we have 4 values in Year 2015,2016,2017,2018

I select 2017 in bar chart  then we have Q1,Q2,Q3,Q4 but one of the quarters has null value but it should not be because Year 2016 has data for all quarters.

In next level of drillldown  say i select Q4 , then one of the months shows blank value which should not be as all months of Q3 has value.

Hopefully i have been able to explain it.

Also i have demonstrated it in my sample app attached above.