Set analysis Count function with additional Count Set Modifier

[Scott2]

Hi QlikSense Community

Hoping you can help with the following. 

For my below sample Data table, I would like to create a "Count" measure in a Pivot Table that Counts only when a certain condition is true.

The condition for when to count is where the rows with the same "RecordId" value all have a non-blank "OpsExplain" value.

So using the below data, only RecordId of "R1" would get counted as it is the only RecordId that has the OpsExplain field populated on every row where RecordId has the same value.

Can this be achieved with a set expression and set modifier as follows? I figured it would need dollar-sign expansion to first expand the inner count functions. I also want it to apply to the curernt selection hence the first dollar in the expression.

Count($<{"$(=Count(RecordId)) = $(=Count(OpsExplain))">} DISTINCT RecordId)

*EDIT added DISTINCT to above

I am new to Qliksense and Set analysis so this is probably miles off! Any help much appreciated.

Data:

LOAD *, Regulation

Inline [

    ConfigFileName, RecordId, OpsExplain, BusinessDate, FirstDiscoveredDate, SubmissionType, AttributeTestType, SourceSystem, AssetClass, RepotingCptyLEI, ReconciliationStatus, Regulation

    "config1.xml", "R1", "ExplainText1", '2023-10-01', '2023-09-15', "TypeA", "TestA", "System1", "Equity", "LEI12345", "Pending", "CFTC-P45"

    "config1.xml", "R1", "ExplainText2", '2023-10-02', '2023-09-16', "TypeB", "TestB", "System2", "Fixed Income", "LEI54321", "Completed", "EMIR"

    "config1.xml", "R1", "ExplainText3", '2023-10-03', '2023-09-17', "TypeC", "TestC", "System3", "Derivatives", "LEI98765", "Pending", "EMIRUK"

    "config1.xml", "R2", "ExplainText4", '2023-10-04', '2023-09-18', "TypeD", "TestD", "System4", "Commodities", "LEI67890", "In Progress", "HKMA"

    "config1.xml", "R2", "ExplainText5", '2023-10-05', '2023-09-19', "TypeE", "TestE", "System5", "Equity", "LEI24680", "Completed", "CFTC-P45"

    "config1.xml", "R2", "", '2023-10-06', '2023-09-20', "TypeF", "TestF", "System6", "Fixed Income", "LEI13579", "Pending", "EMIR"

    "config1.xml", "R3", "ExplainText6", '2023-10-07', '2023-09-21', "TypeG", "TestG", "System7", "Derivatives", "LEI97531", "In Progress", "EMIRUK"

    "config1.xml", "R3", "", '2023-10-08', '2023-09-22', "TypeH", "TestH", "System8", "Commodities", "LEI86420", "Completed", "HKMA"

    "config1.xml", "R3", "", '2023-10-09', '2023-09-23', "TypeI", "TestI", "System9", "Equity", "LEI75319", "Pending", "CFTC-P45"

    "config2.xml", "R1", "ExplainText7", '2023-10-04', '2023-09-18', "TypeD", "TestD", "System4", "Commodities", "LEI67890", "In Progress", "HKMA"

    "config2.xml", "R1", "ExplainText8", '2023-10-05', '2023-09-19', "TypeE", "TestE", "System5", "Equity", "LEI24680", "Completed", "CFTC-P45"

    "config2.xml", "R1", "ExplainText9", '2023-10-06', '2023-09-20', "TypeF", "TestF", "System6", "Fixed Income", "LEI13579", "Pending", "EMIR"

    "config2.xml", "R2", "", '2023-10-01', '2023-09-15', "TypeA", "TestA", "System1", "Equity", "LEI12345", "Pending", "CFTC-P45"

    "config2.xml", "R2", "", '2023-10-02', '2023-09-16', "TypeB", "TestB", "System2", "Fixed Income", "LEI54321", "Completed", "EMIR"

    "config2.xml", "R2", "", '2023-10-03', '2023-09-17', "TypeC", "TestC", "System3", "Derivatives", "LEI98765", "Pending", "EMIRUK"

    "config2.xml", "R3", "", '2023-10-07', '2023-09-21', "TypeG", "TestG", "System7", "Derivatives", "LEI97531", "In Progress", "EMIRUK"

    "config2.xml", "R3", "", '2023-10-08', '2023-09-22', "TypeH", "TestH", "System8", "Commodities", "LEI86420", "Completed", "HKMA"

    "config2.xml", "R3", "ExplainText10", '2023-10-09', '2023-09-23', "TypeI", "TestI", "System9", "Equity", "LEI75319", "Pending", "CFTC-P45"

];

[Cascader]

try this

add the following column to Data table using left join:

Left Join (Data)

LOAD ConfigFileName,RecordId,
OpsExplain,BusinessDate, FirstDiscoveredDate, SubmissionType, AttributeTestType, SourceSystem, AssetClass, RepotingCptyLEI, ReconciliationStatus, Regulation,
if(len(OpsExplain)>0,1,0) as flag
resident Data
;

Then use the following set analysis:

aggr(count({<RecordId=E({<flag={"0"}>})>}RecordId),RecordId)

[Scott2]

*EDIT*

Hi Ameg,

 I used your expression provided and actually it seems I don't need the AGGR function.

I am using count({$<RecordId=E({$<flag={"0"}>})>} DISTINCT RecordId)

I didn't initially appreciate that the  E() function excludes any RecordId that has at least 1 row with flag = 0 . Then the count counts what's not excluded.

It's still not quite working for me though as it seems when used inside the pivot, it selects correctly for the row dimensions but ignores the column dimensions which messes up the counts where the RecordId is across more than one column. As you can see above I added $ symbols to set the scope of the RecordIds to be counted (and to be Excluded) but it still doesn't work. 

Any suggestions?

Thanks!

Scott

Any suggestions?

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/ag...