Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Specialist
Specialist

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

View solution in original post

10 Replies
jonvitale
Creator III
Creator III

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
Specialist
Specialist

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

Anonymous
Not applicable
Author

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
Creator
Creator

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.

Anonymous
Not applicable
Author

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
Specialist
Specialist

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

Anonymous
Not applicable
Author

Thanks Luis,

This really helped.

luismadriz
Specialist
Specialist

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
Creator III
Creator III

and points