Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | |
---|---|
| |
| |
| |
| |
| |
| |
I want all the 'Awaiting Feedback', 'Estimate Completed', 'Estimate Requested' to be counted together and displayed as 'In Progress'
Any help is appreciated.
Thanks
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
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.
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:
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]))
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.
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])
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
Thanks Luis,
This really helped.
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
and points