Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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))
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.
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))
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.