Complicated Expression/Calculated Dimension Problem

[#1]

Hi,

I am trying to create a Pie Chart which displays "Complete" Vs. "Incomplete" for a document called "PMS Checklist"

Here is a sample screenshot of my data and the relevant relationship.

SIDs with document type "PMS Checklist" are complete, and the incomplete ones are SIDs that do not have document type "PMS Checklist" (made up of null values and other SIDs that appear in the data with different Document Types, that don't also have a PMS Checklist row) so i have the calculated dimension:

=if(match([Document Type],'PMS Checklist'), [Document Type])

My expression is "count(distinct SID)"

The problem is that there are other document types which are both completed for PMS Checklist AND for other document types (E.g. SID 105358 has PMS Checklist completed as well as Safety Protocol) and as a result, the Safety Protocol row is being included in the number of SIDs that do not come under the calculated dimension. I have a total of 38 rows extra in the SIDs that don't match "PMS Checklist". If i try to filter out "Safety Protocol" for instance, it also filters out Document Types which are null. These null values make up the majority of incomplete documents.

I hope this all makes sense, any help would be greatly appreciated.

[JonnyPoole · Accepted solution]

I think you want to count SID 105358 towards a 'complete' but not have it count towards 'incomplete'. With that , i would suggest having no dimension in the pie chart and 2 expressions to give you the 2 pie slices.

1st expression for complete is straight forward:

    ->  count(  DISTINCT  if (  [Document Type] = 'PMS Checklist', SID) )

2nd expression for incomplete is a bit more involved:

      -> count(  DISTINCT  if (  SubStringCount(aggr(concat( [Document Type],';'),SID), 'PMS Checklist') = 0, SID) )

Here i am counting the SID only if when i create a delimitted list of [Document Types] for each SID, the list does not contain 'PMS Checklist'.

[#3]

Thanks Jonathan, works like a charm!

[#4]

Hi Jonathan,

Another one for you: suppose i wanted to make a "% Completion" graph of this data Per Week, similar to the graph below:

Is it possible to do this with the two expressions? When i try to create the graph, both expressions don't relate, so i can't make the percentage.

Cheers,

Matt

[JonnyPoole]

NP

i added a sample. I used a mekko but i think a bar would work

Still 2 expressions but now I put each expression divided by the sum of both expressions and format both as %'s.

I think it should work if you add a dimension like week or anything else.