Find the count of rows that matched the conditions in Pick Match Value List

[poklegoguy]

I created a table that looked something as below in Qlik Sense

I don't have the metrics titles in my data model, thus, I used value list as the dimension and the expression is as below:

ValueList('Sales', 'Customer', 'Profit')

For the measures, the 'Values' column have to be calculated and the 'Targets' column is static. Below are the expressions I used:

Pick(Match(ValueList('Sales', 'Customer', 'Profit'), 'Sales', 'Customer', 'Profit'),
SUM(Sales), SUM(Customer), SUM(Profit))

Pick(Match(ValueList('Sales', 'Customer', 'Profit'), 'Sales', 'Customer', 'Profit'),
'>=200', '>=50', '>=50')

Here's the tricky part, I need to find and display the number of metrics that have and haven't met the target, this is what I have tried so far:

SUM(AGGR(Pick(Match(ValueList('Sales', 'Customer', 'Profit'), 'Sales', 'Customer', 'Profit'),
IF(SUM(Sales) >= 200, 1, 0), IF(SUM(Customer) >= 50, 1, 0), IF(SUM(Profit) >= 50, 1, 0)),
ValueList('Sales', 'Customer', 'Profit'), 'Sales', 'Customer', 'Profit')))

Unfortunately, it doesn't work. Can anyone please guide me on how can I achieve this or what is the best way to create a table as the screenshot I shared above so that the expressions wouldn't be too hardcoded so I can find the number of metrics that met the target easier? Appreciate all the help!

[marcus_sommer]

Just try:

Match(ValueList('Sales', 'Customer', 'Profit'), 'Sales', 'Customer', 'Profit'),
IF(SUM(Sales) >= 200, 1, 0), IF(SUM(Customer) >= 50, 1, 0), IF(SUM(Profit) >= 50, 1, 0))

[poklegoguy]

Hi @marcus_sommer, thanks for the reply. I think there's some error in the expression you shared? The if statements wouldn't run without pick function isn't it? I just need to find the total number of metrics that have reach the target and display it in a text object. Sum(Aggr(xxxx,ValueList())) doesn't seem to work for me.

[poklegoguy · Accepted solution]

Managed to find a way to find the number of metrics that meet the target, not the cleanest way but still suffice for now. Just swap the 1 and 0 for the number of metrics that doesn't meet the target.

RANGESUM(IF(SUM(Sales) >= 200, 1, 0), IF(SUM(Customer) >= 50, 1, 0), IF(SUM(Profit) >= 50, 1, 0))

[marcus_sommer]

You are right, I missed the pick() part.

Beside this fields used within a set or total statement or an aggr-dimension must be always native fields and couldn't be calculated - therefore the valuelist() couldn't be applied within the aggr(). But instead using a valuelist() you could use an island-table, like:

t: load pick(recno(), 'Sales', 'Customer', 'Profit') as Dim autogenerate 3;

and then using Dim as dimension.