Sorting with aggr function for rolling 12

[aniketsr]

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‌

[Kushal_Chawda]

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

[aniketsr]

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.

[sunny_talwar]

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)

[aniketsr]

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.

[sunny_talwar]

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)