Set analysis

Mon, 18 Nov 2013 14:04:55 GMT

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?

topic Set analysis in QlikView

Set analysis