Rangesum() in running total Help.

[aniketsr]

Hi Team,

Iam using rangesum and above function and plotting running total against (ID,Year,month).

Consider we have Year as 2015 and 2016.

If we select both the years the running total against the month is coming perfect,

But once i select 2016 the running total get affected as the rows get reduced for 2016.

i'am using the below expression :

=RangeSum(Above(TOTAL Sum({<MONTH=,YEAR=>}Amount),0,12))

Can someone guide me for an expression which should give static result against the respective month even after we select 2016.

Thank You.

Scripting‌

QlikView App Development‌

New to QlikView‌

[sunny_talwar]

You are not seeing this?

[aniketsr]

No i cannot see it, Can you please resend the application.

the actual problem is for 69548

[aniketsr]

Will this function only work in QV12 ?

Actually my bad i dont have QV 12

[sunny_talwar]

Yes, the sorting of Aggr() function was introduced with QV12

The sortable Aggr function is finally here!

Alternative would be to sort intYearMonth in ascending order in the script...

[aniketsr]

Is there any other way we can sought it ?

[sunny_talwar]

Like I mentioned, you will need to fix your sort order in the script to make sure intYearMonth's load order is ascending

[aniketsr]

Hi Sunny,

I installed qv12 & opened the QVW the rolling sum is coming incorrect even i was getting the same issue.

i have highlighted the row in the attached screenshot.

the rolling sum works fine till 2015 Dec for 69548 but from Jan 2016 it goes wrong.

Thanks

[sunny_talwar]

Try this:

Sum(Aggr(RangeSum(Above(

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

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

Sum({$<ActivityType = {'LTC'},Prod1 ={'Commodities'}>}Expense))*-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 Inter-Intra'},Prod1 -={'Commodities'}>} Expense)

+sum({<ActivityType = {'Net Inter Intra - Other','Net Inter Intra - Producing' }>}Expense)

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

))

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

Sum({$<ActivityType = {'Research'}>} Expense)+Sum({$<MasterActivityType = {'CSS'}>} Expense)+sum({<Type ={'C'}>}(Activity_Charge_Adjusted))+sum({<Type = {'R'}>}(Activity_Charge_Adjusted))+Sum(Research_Base_Charge)

),0,12)),SGP#, (intYearMonth, (Numeric))))

[aniketsr]

Hi Sunny,

The values are coming correct if i select both the years , but when i select 2016 the values in "Actual Numerator Rolling 12" come as same as "Actual Numerator"  Column attached is the screenshot .

[sunny_talwar]

How are they same my friend?

You want this to be still cumulative even when 2016 is selected?