topic Re: SET analysis with substringcount and concat in QlikView

SET analysis with substringcount and concat

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: SET analysis with substringcount and concat

effinty2112 — Thu, 02 Jun 2016 21:05:18 GMT

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'.

Re: SET analysis with substringcount and concat

Fri, 03 Jun 2016 08:38:32 GMT

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

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

EDIT: Changed ProgramID to [Project ID]

Re: SET analysis with substringcount and concat

Fri, 03 Jun 2016 21:15:48 GMT

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

Re: SET analysis with substringcount and concat

sunny_talwar — Fri, 03 Jun 2016 22:41:40 GMT

How about this:

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

Re: SET analysis with substringcount and concat

Sun, 05 Jun 2016 14:27:27 GMT

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

Re: SET analysis with substringcount and concat

swuehl — Sun, 05 Jun 2016 21:58:18 GMT

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.

Re: SET analysis with substringcount and concat

Mon, 06 Jun 2016 17:00:12 GMT

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

Thanks!