Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arvindjha2050
New Contributor II

Null values using above function in Qlik

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

Tags (1)
12 Replies
ogautier62
Valued Contributor II

Re: Null values using above function in Qlik

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
New Contributor II

Re: Null values using above function in Qlik

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
Valued Contributor II

Re: Null values using above function in Qlik

OK,

right, I don't see with drilling dimension,

this works if dimensions deployed

agigliotti
Honored Contributor II

Re: Null values using above function in Qlik

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

arvindjha2050
New Contributor II

Re: Null values using above function in Qlik

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
New Contributor II

Re: Null values using above function in Qlik

Hello Andrea,

Any ways around the same?

Thanks

agigliotti
Honored Contributor II

Re: Null values using above function in Qlik

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-sds
Contributor

Re: Null values using above function in Qlik

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

arvindjha2050
New Contributor II

Re: Null values using above function in Qlik

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.

Community Browser