Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

zalemam1
New Contributor

How to Count and Group different values in a column?

Hi All,

New to Qlik Sense, and manipulation of data.

I'm trying to count certain values in a column then have them grouped up and displayed as a sum of one number.

For example:

This is what values are in a column:

L0 Status
Awaiting Feedback from Business/BA
Estimate Completed
Estimate Requested (Past Due Date)
Request Approved
Request Approved (Funded Supplier)
Cancelled

I want all the 'Awaiting Feedback', 'Estimate Completed', 'Estimate Requested' to be counted together and displayed as 'In Progress'

Any help is appreciated.

Thanks

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: How to Count and Group different values in a column?

Hi Zaid,

Do all calculations in the same dimension at once. You have many options with IF, Pick, =, etc. It's a matter of not only style but also maintainability later on when you or someone else may struggle to remember/understand what happened there.

This is just an example for that dimension:

=Pick(WildMatch([L0 Status],'Awaiting Feedback*', 'Estimate Completed*', 'Estimate Requested*','Request Approved*',[L0 Status]),

                            'In Progress'       , 'In Progress'        ,  'In Progress'       ,'Complete'         ,[L0 Status])

As for the measure, you don't need to redo this, just a regular Count(Measure) or any other aggregation

I hope this helps,

Cheers,

Luis

Untitled.png

10 Replies
jonvitale
Contributor III

Re: How to Count and Group different values in a column?

Do you have access to the load script? If so, I would suggest creating a new field that summarizes this field in a way that makes sense to you. So somewhere where you are loading L0 status, you'd have a line like:

Load

/// all your other loading stuff

If("L0 Status" = 'Awaiting Feedback'  or "L0 Status" = 'Estimate Completed' or "L0 Status" = 'Estimate Requested', 'Pre-Approval', IF("L0 Status" = 'Request Approved' or "L0 Status" = 'Request Approved (Funded Supplier)', 'Cancelled')) as "L0 Category"

TableName

Then instead of using L0 status as your dimension, you'd use the L0 category.

By the way, there is a slightly nicer way to do this with a Mapping Table, if there are a lot of changes you need to make.

luismadriz
Valued Contributor

Re: How to Count and Group different values in a column?

HI,

You could create a new dimension with the following:

=If(WildMatch([L0 Status],'Awaiting Feedback*', 'Estimate Completed*', 'Estimate Requested*',[L0 Status])<=3,'In Progress',[L0 Status])

It'll look like this:

Untitled.png

zalemam1
New Contributor

Re: How to Count and Group different values in a column?

Thanks Luis,

This works fine for the grouping part.

How do I get it to count the number of "In Progress" items?

I'm trying to use the count function but I get an Invalid dimension error

=If(WildMatch([L0 Status],'Awaiting Feedback from Business/BA*', 'Estimate Completed*', 'Estimate Requested*', 'Estimate Requested (Past Due Date)*',[L0 Status])<=4,'In Progress',[L0 Status])

=count(if(match([L0 Status],'In Progress'),[L0 Status]))

sarahplymale
Contributor

Re: How to Count and Group different values in a column?

The count of the "In Progress" items (or items of another status) should be a measure not a dimension. 

The dimension would be your new grouping of the different statuses (In Progress, Canceled, etc...) then the measure would be count([LO Status]) and it should count the number in each status group.

zalemam1
New Contributor

Re: How to Count and Group different values in a column?

Luis,

Is it possible to group other things as well in the same column?

I have two value types I want grouped into "Completed"

Adding this to the dimension doesnt seem to work.

=If(WildMatch([L0 Status],'Request Approved*', 'Request Approved (Funded Supplier)*', [L0 Status])<=2,'Complete',[L0 Status])

luismadriz
Valued Contributor

Re: How to Count and Group different values in a column?

Hi Zaid,

Do all calculations in the same dimension at once. You have many options with IF, Pick, =, etc. It's a matter of not only style but also maintainability later on when you or someone else may struggle to remember/understand what happened there.

This is just an example for that dimension:

=Pick(WildMatch([L0 Status],'Awaiting Feedback*', 'Estimate Completed*', 'Estimate Requested*','Request Approved*',[L0 Status]),

                            'In Progress'       , 'In Progress'        ,  'In Progress'       ,'Complete'         ,[L0 Status])

As for the measure, you don't need to redo this, just a regular Count(Measure) or any other aggregation

I hope this helps,

Cheers,

Luis

Untitled.png

zalemam1
New Contributor

Re: How to Count and Group different values in a column?

Thanks Luis,

This really helped.

luismadriz
Valued Contributor

Re: How to Count and Group different values in a column?

Cheers,

PS. When possible please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

jonvitale
Contributor III

Re: How to Count and Group different values in a column?

and points

Community Browser