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: 
Not applicable

SET analysis with substringcount and concat

I have a chart w/ 4 dimensions and 7 expressions:

My expression all the way to the right ("no implementation milestone") is supposed to count projects (by ID) that do not have an implementation milestone (there are multiple milestones per project and an implementation milestone would be marked "yes" in the "TaskImpMilestone" field).  Also-- I only want to count projects with a status of "Started" and a taxonomy of "Investment".

I was able to identify those projects in a separate chart by concatenating the TaskImpMilestone field and counting everything that did not have "Yes" in the string.. Now when I try to add the same set analysis below to the chart pictured above it is counting projects from all taxonomies and statuses other than "Investment" and "Started"..

=If(SubStringCount(concat({<ProjectTaxonomy={'Investment'},ProjectWorkStatus={'Started'}>} DISTINCT TaskImpMilestone),'Yes')=1,0,1)

Anyone have any ideas?

Thanks

7 Replies
effinty2112
Master
Master

Hi Julian,

Try:

Count({$<ProjectTaxonomy={'Investment'},ProjectWorkStatus={'Started'}, TaskImpMilestone ={"<> 'yes'"}>}ProgramID)

Good Luck

Andrew

PS In the bracket {"<> 'yes'"} make sure the case of 'yes' is correct. I can't remember if this is case sensitive. In your question it wasn't quite clear if this field was marked as 'yes' or 'Yes'.

Not applicable
Author

Or

Count({$<ProjectTaxonomy={'Investment'},ProjectWorkStatus={'Started'}, TaskImpMilestone =-{'yes'}>}[Project ID])

(please note the - before {'yes'} )

EDIT: Changed ProgramID to [Project ID]

Not applicable
Author

Neither of those returned any records for me..  I don't think those would work anyways because it's not just the ones that <> 'Yes' that I'm looking for.. some projects would have nulls as they do not have any milestones at all...

sunny_talwar

How about this:


Count({$<ProjectTaxonomy={'Investment'}, ProjectWorkStatus={'Started'}, ProgramID = e({<TaskImpMilestone ={'Yes'}>})>} ProgramID)

Not applicable
Author

Sunny-- This got me a little closer but my total is still off..

My other table that I'm using to compare has 748..

Thanks,

Julian

swuehl
MVP
MVP

Maybe something like

Count(

{$<ProjectTaxonomy={'Investment'}, ProjectWorkStatus={'Started'},

[Project ID] = e({<TaskImpMilestone ={'Yes'}>}) >}

DISTINCT [Project ID])

But with set analysis, it's vital that we know how the fields that are used in the set analysis are related to each other and what the possible value range of these sets are.

If nothing of above really works out, it's much more easy to help if you can post a small sample QVW.

I can't really see why your expression you are using successfully in your one table shouldn't work in the other as well, so I think we are missing the full picture here.

Not applicable
Author

That one seems to have worked.. the counts match.

Thanks!