8 Replies Latest reply: Mar 26, 2015 9:09 AM by Edwin Witvoet RSS

    'if statement' within Valuelist

    Edwin Witvoet

      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?

        • Re: 'if statement' within Valuelist
          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])))

            • Re: 'if statement' within Valuelist
              Edwin Witvoet

              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

                • Re: 'if statement' within Valuelist
                  Gysbert Wassenaar

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

                   

                  Perhaps you can upload an example?

                    • Re: 'if statement' within Valuelist
                      Edwin Witvoet

                      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

                        • Re: 'if statement' within Valuelist
                          Gysbert Wassenaar

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

                            • Re: 'if statement' within Valuelist
                              Edwin Witvoet

                              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

                                • Re: 'if statement' within Valuelist
                                  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])
                                          )
                                      )
                                  )