How to calculate % expression by a specific variable in a straight table

[#1]

Hi,

In the attached excel file, I have provided an example of  what I am trying to do in my qlikview dashboard. But I am not able to come up with  a solution to achieve it. If someone can help me, that'd be a big help.

Explanation of what I want to achieve

- There are two dimensions 'Agent Name' and 'Date' associated with the 'Agent Name'. Then there is one expression column 'Availabitly %' which gives a percentage value. I need to create another expression, which first checks if the 'Availabitly %'value is greater than 80% and if yes then count the date for that particular row divide it by total count of # of dates for that particular agent.

Thanks!

Akash

[sunny_talwar]

May be this:

Count(If(Availability% > 0.80, Date, 0))/Count(DISTINCT TOTAL <[Agent Name]> Date)

[#3]

Hi Sunny,

So I tweaked a bit of your logic i.e. =if([Availability %]>=0.84,Count(Date1)/ Count(DISTINCT TOTAL <AgentName> Date1),0) and it worked for individual rows. But for the subtotal per each agent, like the one in attached excel file, what shallI do?

Akash

[Anonymous]

Try this, is the same, but appears the zero:

count({<[Availabitly %]={">80"}>}1)/count(Total <[Agent Name]> Date)

[sunny_talwar]

May be this:

=Sum(Aggr(If([Availability %]>=0.84,Count(Date1)/ Count(DISTINCT TOTAL <AgentName> Date1), 0), AgentName, Date))

[#6]

Didn't work. Gave me all 0's on each row instead of giving correct value. The subtotal is basically sum of all the individual value for each agent. If I convert the straight table to Pivot, I have an option to do the partial sum by Agent Name, but that also doesn't help. Btw the actual table has multiple expression apart from the two I explained. But I don't think that can be a problem in showing the partial sum..can it?

[Anonymous]

had you apply this one:

count({<[Availability %]]={">80"}>}1)/count(Total <[Agent Name]> Date) ?

[sunny_talwar]

Would you be able to share a sample or image of what you are seeing?

[#9]

Hi,

Yes I tried your logic too but didn't work. Using your logic, irrespective of [Availability %] is >80 or not,  it count the value as 1.

Any other way I can do with set analysis. The [Availability %] is an expression which is again calculated based on another expression. Not sure if that's throwing an error in calculation