'if statement' within Valuelist

[edwinwitvoet]

I have a valuelist which looks like

if(ValueList('Products', 'Sales')='Products', sum({<color*={'yellow'}>} [y_quantity]),

if(ValueList('Products', 'Sales')='Sales', sum({<color*={'yellow'}>} [sales]))

Now I want to extend the valuelist expressions to conditionally sum another field, like

if(ValueList('Products', 'Sales')='Products',

     /* if color = yellow */ sum({<color*={'yellow'}>} [y_quantity]),

     /* if color = red */ sum({<color*={'red'}>} [r_quantity]),

     /* if color = green */ sum({<color*={'green'}>} [g_quantity]),

if(ValueList('Products', 'Sales')='Sales', sum({<color*={'yellow'}>} [sales]))

Any suggestions how to add these conditional statements?

[Gysbert_Wassenaar]

if(ValueList('Products', 'Sales')='Products',

     pick(match(color, 'yellow','red','green'),

          sum({<color*={'yellow'}>} [y_quantity]),

          sum({<color*={'red'}>} [r_quantity]),

          sum({<color*={'green'}>} [g_quantity])),

if(ValueList('Products', 'Sales')='Sales', sum({<color*={'yellow'}>} [sales])))

[edwinwitvoet]

Hi Gijsbert,

Thanks for the quick response.

The Pick/Match works fine outside the valuelist (like as a measure in a table).

Inside the ValueList it does not work. I assume it has something the do with the aggregate functions I use in the various pick - expressions.

Note : when I filter the data such that only the matched colors are available, it does work. But whithout the filter (e.g. also data for other colors is available) it does not work

Looking forward to your advice

[Gysbert_Wassenaar]

Does the chart where you use the expression have color as one of its dimensions?

Perhaps you can upload an example?

[edwinwitvoet]

No, actually it does not.

I use the Valuelist in the context of a Pivot Table (released in Qlik Sense V1.1).

When I unfold all rows, I do see the values in the unfolded rows, but it is now summed up in the above levels

Let me prepare an example indeed

[Gysbert_Wassenaar]

Well, if you don't use color as a dimension then there are no color values in your chart to match.

[edwinwitvoet]

Hi Gijsbert, I stripped the app and attached it.

You will see 2 flavors of the KPI table.

One with and one without the Pick/Match

Thanks in advance for your advice

[Gysbert_Wassenaar · Accepted solution]

To be honest I don't really understand what you're trying to do. But as I said above you're using a dimension in your expression that's not a chart dimension. And if you use that in an if or a pick-match then it doesn't have a single value, i.e. only(metric_id) would return null. I reckon you'll have to use the aggr function. Something like this maybe;

if(Campaign='Campaign 1',
    pick(match(ValueList('Reach', 'Views', 'Plays', 'Sales'),'Reach', 'Views', 'Plays', 'Sales'),
        sum(aggr(
            pick(match(metric_id, 6, 40),
                sum({<ga_metric_code*={'ga:pageviews'}>} [ds_value]),
                sum({<resource_type*={'FB Post'}>} [ds_value])
          ),Campaign,metric_id)),
        sum({<yt_metric_code*={'views'}, resource_name*={'*let me feel*'}>} [ds_value]),
        sum({<sc_metric_code*={'playback_count'}, resource_name*={'*let me feel*'}>} [ds_value]),
        Count([to_id])
  )
,
    if(Campaign='Campaign 2',
        pick(match(ValueList('Reach', 'Views', 'Plays', 'Sales'),'Reach', 'Views', 'Plays', 'Sales'),
            sum({<ga_metric_code*={'ga:pageviews'}>} [ds_value]),
            sum({<yt_metric_code*={'views'}, resource_name*={'*miami*'}>} [ds_value]),
            sum({<sc_metric_code*={'playback_count'}, resource_name*={'*miami*'}>} [ds_value]),
            Count([to_id])
        )
    )
)

[edwinwitvoet]

Thanks Gysbert, works!

I will work on refactoring the datamodel, but for now this works. Thanks!