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

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

Mon, 06 Feb 2017 16:57:47 GMT

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

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

sunny_talwar — Mon, 06 Feb 2017 17:22:25 GMT

May be this:

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

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

Mon, 06 Feb 2017 17:42:16 GMT

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

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

Anonymous — Mon, 06 Feb 2017 17:55:59 GMT

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

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

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

sunny_talwar — Mon, 06 Feb 2017 18:01:47 GMT

May be this:

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

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

Tue, 07 Feb 2017 00:25:12 GMT

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?

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

Anonymous — Tue, 07 Feb 2017 09:33:49 GMT

had you apply this one:

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

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

sunny_talwar — Tue, 07 Feb 2017 10:16:04 GMT

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

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

Wed, 08 Feb 2017 21:34:01 GMT

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