Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Set analysis

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?

Tags (1)