Getting DISTINCT COUNT of Dimension values based on condition with aggregation in Qlik expression for KPI

[NattiNatey]

Suppose I have a table called DataTable with column [Dim1] and Measure [Num1]. I want to get all distinct values in [Dim1] based on the condition that [Num1] aggregated by [Dim1] > 500.

This can be done with SQL like this:

SELECT COUNT (DISTINCT [Dim1]) FROM
    (SELECT [Dim1], SUM([Num1])
    FROM DataTable GROUP BY [Dim1]
    HAVING SUM([Num1]) >= 500)

Ideally I would like to do this in an expression for a new measure and display the count in a KPI, but I have not been able to get it working with aggregation. For example I tried the following, but it seems to filter the rows BEFORE aggregation, so the result is not correct.

Count(
  Distinct
  If(
    Aggr(Sum([Num1]), [Dim1]) >= 500,
    [Dim1]
  )
)

I am new to Qlik and any suggestions are greatly appreciated!

[marcus_sommer · Accepted solution]

You may try:

If(sum(Aggr(Sum([Num1]), [Dim1])) >= 500, Count(Distinct [Dim1]))

[NattiNatey]

Thanks @marcus_sommer this solution worked! I realized I am having an additional issue with adding an alternate state filter to the query, but this is unrelated to the question asked.