Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Akey_Qlik
New Contributor II

Struggling with aggregation

Hi Guys,

Well, I have a scenario , where I have to show the count of current month active product in a locale which have a rating above 4.3 across all the months aggregated over market. 

So For Ex - So suppose there is a product A which is active in the Locale CA and TX for october under north America and in active  under any locale in north america ,  so we need to take their rating sum and reviews and calculate their rating and if rating is greater than 4.3 , we would take the count as 1 for whole NA  for that product.  This product locale combination can be active under any other month as well, so i have to check in other months as well with the same condition and if it satisfies I have to show that count rolled in october month only. so basically it's a accumulated sum. 

Below is my expression - It checks the prod locale key , but while aggregating as I'm have to do it on the  product id, market level it only takes the product  and aggregates  over all the locale and gives me the count. so I'm getting some extra numbers.  Can any one help me ? 

Attached is the table required

Sum({<AsOfPeriod=>}Aggr(if(
Count(DISTINCT {<AsOfPeriod=, NewIndex={"$(=max(_AsOfMonthIndex))"},PeriodType={'Current month'}>}%ProdLocalMarket)>0,
if((Sum({<AsOfPeriod=,PeriodType={'Current month'}>}RatingSum)/Sum({<AsOfPeriod=,PeriodType={'Current month'}>}Reviews))>=4.25,1,0)),AsOfPeriod,ProductID,Market))

 

 

Note - All the AsOfPeriod Months are attached to the months below them to have the accumulated view in the period type calendar , also to them as period type current month. 

Idea is that user can switch between current view and lifetime view. 

 

 

3 Replies

Re: Struggling with aggregation

Initially if you try below one, what will happen

Sum(aggr(If(
Count(DISTINCT {<AsOfPeriod=, NewIndex={"$(=max(_AsOfMonthIndex))"},PeriodType={'Current month'}>}%ProdLocalMarket)>0,
if((Sum({<AsOfPeriod=,PeriodType={'Current month'}>}RatingSum)/Sum({<AsOfPeriod=,PeriodType={'Current month'}>}Reviews))>=4.25,1,0)), AsOfPeriod,ProductID,Market))
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Akey_Qlik
New Contributor II

Re: Struggling with aggregation

It still considers all the locales for the products in that market if any and then calculate the rating.

Re: Struggling with aggregation

Perhaps this?
Sum({<Local = {'CA', 'TX'}, month ={'oct'}>} aggr(NoDistinct If(
Count(DISTINCT {<AsOfPeriod=, NewIndex={"$(=max(_AsOfMonthIndex))"},PeriodType={'Current month'}>}%ProdLocalMarket)>0,
if((Sum({<AsOfPeriod=,PeriodType={'Current month'}>}RatingSum)/Sum({<AsOfPeriod=,PeriodType={'Current month'}>}Reviews))>=4.25,1,0)), AsOfPeriod,ProductID,Market))
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)