Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
Count(If(Availability% > 0.80, Date, 0))/Count(DISTINCT TOTAL <[Agent Name]> Date)
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
Try this, is the same, but appears the zero:
count({<[Availabitly %]={">80"}>}1)/count(Total <[Agent Name]> Date)
May be this:
=Sum(Aggr(If([Availability %]>=0.84,Count(Date1)/ Count(DISTINCT TOTAL <AgentName> Date1), 0), AgentName, Date))
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?
had you apply this one:
count({<[Availability %]]={">80"}>}1)/count(Total <[Agent Name]> Date) ?
Would you be able to share a sample or image of what you are seeing?
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