24 Replies Latest reply: Jun 3, 2016 1:51 PM by Sunny Talwar

# Trying to write expressions but getting error

I am trying to count the employee, who are above or below the median salary.

The expressions I am using is as follows:

Count({<[CTC]={">="} Median({<[CTC]>} [Employee])

• ###### Re: Trying to write expressions but getting error

May be this:

Above Median

Count({<Employee = {"=CTC > Median(TOTAL CTC)"} Employee)

Below Median

Count({<Employee = {"=CTC < Median(TOTAL CTC)"} Employee)

• ###### Re: Trying to write expressions but getting error

Thanks for quick response.

I am still not able to get the count. Let me give you more clarity / understanding of data.

I have a column name "Employee Name" & "CTC (USD)". Now trying to write expression on Qlik Sense to get count of employee who are above or below median CTC.

Have tried this: Count({<[Employee Name] = {"=CTC > Median[CTC (USD)])"} [Employee Name])

but didn't work.

• ###### Re: Trying to write expressions but getting error

you sure.. you tried this, I mean it is working for me..

Above Median

=count(if(CTC>Median(Total CTC),Employee ))

Below Median

=count(if(CTC<Median(Total CTC),Employee ))

• ###### Re: Trying to write expressions but getting error

Thanks Kamal !!! The given formula is working absolutely fine however still not solving the purpose.

Its giving the count of employee, who are above or below the median but calculating the median of entire population. For example: Data has all job levels like: Executive, Senior Executive till higher leadership. If we compare the CTC with higher leadership with junior management then this will not the right way of analysis.

Can it be possible that the median / average automatically changed when we draw a graph at Job level?? I believe this is due to  "Median(Total CTC)".

• ###### Re: Trying to write expressions but getting error

Try this:

count(if([CTC (USD)] > Median(<Job Level>[CTC (USD)])]),[Employee Name]))

I have modified it to take care of Job Level as well.. Pls give it a try just put the name of the column which holds the Job Level inside the angle brackets "<>".

• ###### Re: Trying to write expressions but getting error

@ Kamal: Tried this but getting expression error:

count(if([CTC (USD)] > Median([M_Grade] [CTC (USD)])]),[Employee Name]))

Just replace <Job Level> by [M_Grade].

• ###### Re: Trying to write expressions but getting error

you have to put this inside angle brackets:

• ###### Re: Trying to write expressions but getting error

Tried this as well but no luck :-(

count(if([CTC (USD)] > Median(<[M_Grade]>[CTC (USD)])]),[Employee Name]))

• ###### Re: Trying to write expressions but getting error

Did you try to rectify my mistake (pointed out by kamal?

Count({<[Employee Name] = {"=[CTC (USD)] > Median(TOTAL [CTC (USD)])"}>} [Employee Name])

• ###### Re: Trying to write expressions but getting error

Sunny Thanks.. Now expression error has gone but the same issue which I have been asking. The given expression calculating the median of entire population then median value applicable on all job levels. Which is incorrect analysis. When I draw a graph and taking Job level on the X axis then it should count the employee who are below or above median on each job levels individually without keeping one median for all.

Hope, I may conveyed the concern. Thanks for your support and expect more on this please.

• ###### Re: Trying to write expressions but getting error

May be this:

Count({<[Employee Name] = {"=[CTC (USD)] > Median(TOTAL <[Job Level]> [CTC (USD)])"}>} [Employee Name])

• ###### Re: Trying to write expressions but getting error

Tried this but as it was earlier:

Count({<[Employee Name] = {"=[CTC (USD)] > Median(TOTAL <[M_Grade]> [CTC (USD)])"}>} [Employee Name])

Median value remains the same for all job levels.

• ###### Re: Trying to write expressions but getting error

Not sure what is M_Grade, but the sample from other post seems to not have that field. For total, it seems to be working

• ###### Re: Trying to write expressions but getting error

M_Grade = Job in original file. M trying to explain you what I am looking for with the dummy data reference.

Average of total data = 73919, Below average Job "A" count = 147........& expression working fine to get this data and so on....

But what I am looking for is.

Average of Job "A" = 76098, Below average Job "A" count = 154 and so on...

I mean, when I draw a Bar Graph and keep Job at x axis then all Job should have a different averages and below average count will be calculated accordingly..

Hope this clarifies my concern..

• ###### Re: Trying to write expressions but getting error

Kaushal would you be able to provide in Excel the exact numbers you would want to see in the bar chart. I think not knowing what you need exactly might be causing the problem.

• ###### Re: Trying to write expressions but getting error

I guess if the name of the column is CTC (USD), then try this:

Count({<[Employee Name] = {"=[CTC (USD)] > Median(TOTAL [CTC (USD)])"} [Employee Name])

• ###### Re: Trying to write expressions but getting error

Thanks Sunny for your response but this is still not working. Writing Median command under " " will not get identified by the application.

Also, can we avoid the "Total" Command as this will be resulting wrong analysis. Please suggest,

• ###### Re: Trying to write expressions but getting error

Would you be able to share a sample to show where this isn't working?

• ###### Re: Trying to write expressions but getting error

I have shared the dummy data on another post as unable to attached the same in this post.

• ###### Re: Trying to write expressions but getting error

I think ">}" is missing (Set and modifier are not closed) Just before the Employee Name in the expression. May be that can be the cause expression provided by Sunny is not working..  What you think..?

• ###### Re: Trying to write expressions but getting error

Try this:

Above Median

=count(if(CTC>Median(Total CTC),Employee ))

Below Median

=count(if(CTC<Median(Total CTC),Employee ))

• ###### Re: Trying to write expressions but getting error

count(if([CTC (USD)] > aggr(Median([CTC (USD)]),[Job Type]),[Employee])

• ###### Re: Trying to write expressions but getting error

@Sunny: Please refer the above chart & table which is prepared based on the dummy data I have shared.

• ###### Re: Trying to write expressions but getting error

My numbers are slightly off, but here is what I have for you