2 Replies Latest reply: Feb 17, 2016 1:35 PM by Wagner Morandi RSS

    Google analytics set expression

      Hello!

       

      I'm building a Qlikview to display some information from Google analytics, but I'm having some problems with a set expression.

       

      I have the following fields that are relevant:

      Data_dim_medium (this holds 'organic' for organic traffic and 'cpc' for paid traffic)

      Data_date_qv (this holds the dates, for now 30 dates, 2012-09-01 through 2012-09-30)

      Doublewords (A table containing all keywords that are present both as organic and paid, meaning people used these keywords to get to my site, but I also buy these keywords).

      Data_metric_visits (number of visits to my site)

       

      Now what I want is a list with Doublewords as dimension, showing organic visits when ads were off, and organic visists when ads are on.

       

      DoublewordsOrganic visits ad onOrganic visits ad offTotal organic visitsTotal Paid visists
      word112132515
      word211162613
      etc.

       

       

      The below expression (Organic visists ad on) works if I select a specific word from the list Doublewords, but not as a pivot chart with doublewords as a dimension. What should I change?

       

      Organic visists ad on:

      =Sum({$<Data_dim_medium={'organic'},Data_date_qv=P({<Data_dim_medium={'cpc'}>}Data_date_qv)>}Data_metric_visits)

       

      Total organic visists:

      =SUM({<Data_dim_medium={'organic'}>} Data_metric_visists)

       

      Total paid visists:

      =SUM({<Data_dim_medium={'cpc'}>} Data_metric_visists)

       

       

      Any advice would be greatly appreciated!

      Thank you