topic Re: Sorting with aggr function for rolling 12 in QlikView

Sorting with aggr function for rolling 12

aniketsr — Wed, 25 Nov 2020 16:16:04 GMT

Hi qlikers,

I have a problem in the rolling 12 months where in even after sorting the columns in the script the values are not coming as expected.

iam attaching a sample qvw and data for reference.

kush141087‌

Re: Sorting with aggr function for rolling 12

Kushal_Chawda — Sat, 02 Sep 2017 10:29:45 GMT

No need to do grouping in script, try below expression

=sum(aggr(RangeSum(Above(

Round(

sum(total <Region,intYearMonth>Sales)

,0.000000000001),0,12))*Avg(1),Region,intYearMonth))

Re: Sorting with aggr function for rolling 12

aniketsr — Sat, 02 Sep 2017 15:31:13 GMT

Hi kushal ,

Thanks for your help i have implemented the same script in the actual formula but the values are coming Zero.

I might be wrong somewhere hence iam attaching the application for reference.

Re: Sorting with aggr function for rolling 12

sunny_talwar — Sat, 02 Sep 2017 19:12:32 GMT

May be this

=Aggr(RangeSum(Above(if(Match(Prod1,'Equities','Syndicate'),Sum(EV) +(Sum({$<ActivityType = {'BCE'}>} Expense) *-1),

Sum(EV) +(if(vLTC=1,if(Match(Prod1,'Equities','Syndicate'),'',Sum({$<ActivityType = {'LTC'},Prod1 -={'Commodities'}>}Expense)*-1) +

Sum({$<ActivityType = {'LTC'},Prod1 ={'Commodities'}>}Expense),0)*-1) +(Sum({$<ActivityType = {'BCE'}>} Expense) *-1))

(

(Sum({$<ActivityType = {'Sales Direct','Direct'},Prod1 -={'FI Syndicate','Commodities'}>} Expense)+ sum({<EmployeeType = {'Sales','Sales Trader'}>}Cost)

+ Sum({$<ActivityType = {'Sales Direct'},Prod1 ={'Commodities'}>} Expense) )

(Sum({$<ActivityType = {'Sales Inter-Intra'},Prod1 -={'Commodities'}>} Expense)+sum({<ActivityType = {'Net Inter Intra - Other','Net Inter Intra - Producing' }>}Expense))

(

Sum({$<ActivityType = {'Research'}>} Expense)+Sum({$<MasterActivityType = {'CSS'}>} Expense)+

Sum({<ActivityType ={'Product Access','Analyst Activity','Corp Access'}>}CostByProduct)/1000

)

Sum({$<ActivityType = {'Trading'} ,Prod1 -={'Inv. Services'}>} Expense)

), 0, 12)), Region, intYearMonth)

Re: Sorting with aggr function for rolling 12

aniketsr — Mon, 11 Sep 2017 15:26:51 GMT

Hi Sunny,

Thank you for the guidance , the expression is working fine only when Year and month are not selected.

But when we select Year and month the Rolling month value is same as that of the Actual sales value.

Basically the rolling 12 month value should be same even after selecting Year and Month.

Re: Sorting with aggr function for rolling 12

sunny_talwar — Mon, 11 Sep 2017 15:37:02 GMT

May be try this

=Aggr(RangeSum(Above(if(Match(Only({1} Prod1),'Equities','Syndicate'),Sum({<Year, MONTH_SHORT_NAME>}EV) +(Sum({$<ActivityType = {'BCE'}, Year, MONTH_SHORT_NAME>} Expense) *-1),

Sum({<Year, MONTH_SHORT_NAME>}EV) +(if(vLTC=1,if(Match(Only({1} Prod1),'Equities','Syndicate'),'',Sum({$<ActivityType = {'LTC'},Prod1 -={'Commodities'}, Year, MONTH_SHORT_NAME>}Expense)*-1) +

Sum({$<ActivityType = {'LTC'},Prod1 ={'Commodities'}, Year, MONTH_SHORT_NAME>}Expense),0)*-1) +(Sum({$<ActivityType = {'BCE'}, Year, MONTH_SHORT_NAME>} Expense) *-1))

(

(Sum({$<ActivityType = {'Sales Direct','Direct'},Prod1 -={'FI Syndicate','Commodities'}, Year, MONTH_SHORT_NAME>} Expense)+ sum({<EmployeeType = {'Sales','Sales Trader'}, Year, MONTH_SHORT_NAME>}Cost)

+ Sum({$<ActivityType = {'Sales Direct'},Prod1 ={'Commodities'}, Year, MONTH_SHORT_NAME>} Expense) )

(Sum({$<ActivityType = {'Sales Inter-Intra'},Prod1 -={'Commodities'}, Year, MONTH_SHORT_NAME>} Expense)+sum({<ActivityType = {'Net Inter Intra - Other','Net Inter Intra - Producing' }, Year, MONTH_SHORT_NAME>}Expense))

(

Sum({$<ActivityType = {'Research'}, Year, MONTH_SHORT_NAME>} Expense)+Sum({$<MasterActivityType = {'CSS'}, Year, MONTH_SHORT_NAME>} Expense)+

Sum({<ActivityType ={'Product Access','Analyst Activity','Corp Access'}, Year, MONTH_SHORT_NAME>}CostByProduct)/1000

)

Sum({$<ActivityType = {'Trading'} ,Prod1 -={'Inv. Services'}, Year, MONTH_SHORT_NAME>} Expense)

), 0, 12)), Region, intYearMonth)