topic Re: How to write expression to Count([Job ID]) by another dimension in QlikView

How to write expression to Count([Job ID]) by another dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Here's what i'm trying to achieve.

I have a list of jobs. I can count the number of jobs by using Count([Job ID]) as the expression. I'm creating a table that shows those counts as the expression with a dimension of Zone Name. But of those jobs, some were completed, some were cancelled and some were lost. I want to create a table with columns for each of those, so like a column with Count([Job ID]) for completed, another column with Count([Job ID]) for cancelled and another column with Count([Job ID]) for closed.

I know this is probably incredibly simple, but i'm just not sure about the syntax for how to write the expression. Can someone help me?

Thank you!

Chris

Re: How to write expression to Count([Job ID]) by another dimension

groveliam — Fri, 10 Aug 2018 14:07:45 GMT

I don't know what your field is called so lets say Status can be completed or closed.

Count({<Status = {'completed'}>}[Job ID])

Count({<Status = {'closed'}>}[Job ID])

Re: How to write expression to Count([Job ID]) by another dimension

PabloTrevisan — Fri, 10 Aug 2018 14:09:02 GMT

You can use

Count({<Status = {'completed'}>}distinct [Job ID]) to count only one for each ID.

Re: How to write expression to Count([Job ID]) by another dimension

Anonymous — Fri, 10 Aug 2018 14:20:10 GMT

Thank you. Do you feel like taking it a step farther for me? I also then want to be able to create another column that measures the completion percentage. So this would simply be the Completed column we just created, divided by the total column that I already had that is just Count([Job ID]).

Do you know how I can write this and display it as a percentage?

Re: How to write expression to Count([Job ID]) by another dimension

groveliam — Fri, 10 Aug 2018 14:22:27 GMT

Gotcha

Count({<Status = {'completed'}>}[Job ID]) / Count([Job ID])