topic Re: Struggling with aggregation in QlikView

Struggling with aggregation

Akey_Qlik — Sun, 09 Dec 2018 10:30:31 GMT

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.

Re: Struggling with aggregation

Anil_Babu_Samineni — Sun, 09 Dec 2018 11:18:28 GMT

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))

Re: Struggling with aggregation

Akey_Qlik — Sun, 09 Dec 2018 11:38:16 GMT

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

Re: Struggling with aggregation

Anil_Babu_Samineni — Sun, 09 Dec 2018 12:54:29 GMT

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))