0 Replies Latest reply: Nov 18, 2013 9:04 AM by Dariya Kovalyuk RSS

    Set analysis

    Dariya Kovalyuk

      Hi, everyone!

      I need to count outlets that bought both of brands in a year in each month separately and in total.

      Value for each brand I take from alternate states.

      I tried this, but I had to use different formulas for months and total.

       

       

      if (Dimensionality()=1,


      SubStringCount(concat(aggr(if(Sum({$*[Brand Old]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]) and
      Sum({$*[Brand New]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]),[Outlet_code]&'|'),[Outlet_code],Month)), '|')),

       

      SubStringCount(concat(aggr(if(Sum({$*[Brand Old]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]) and

      Sum({$*[Brand New]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]),[Outlet_code]&'|'),[Outlet_code])), '|'))

      )

       

      And when I use if statment, it takes a lot of time to generate my report. I need to make it faster.

      Any ideas how can I make this formula simpler?