Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
edwinwitvoet
Contributor III
Contributor III

'if statement' within Valuelist

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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])

        )

    )

)


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
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])))


talk is cheap, supply exceeds demand
edwinwitvoet
Contributor III
Contributor III

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?


talk is cheap, supply exceeds demand
edwinwitvoet
Contributor III
Contributor III

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.


talk is cheap, supply exceeds demand
edwinwitvoet
Contributor III
Contributor III

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

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])

        )

    )

)


talk is cheap, supply exceeds demand

View solution in original post

edwinwitvoet
Contributor III
Contributor III

Thanks Gysbert, works!

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