Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

Cumulative Sum Help!

Hi Folks,

I need to cumulative sum on bar chart , below is my expression:

=num(Money(sum(aggr(sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate), CurrencyIsoCode, Month))/1000000),'####')

I'm trying Something like below , but it's not working:

=aggr( rangesum(above( sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate), 0, rowno() )), CurrencyIsoCode, Month)

Thanks,

AS

23 Replies
sunny_talwar

What expression are you using to get this current scenario chart?

amit_saini
Master III
Master III
Author

I'm sorry you are right:

Expression is :

=num(Money(sum(aggr(sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate), UP2GO_2F__pkl_ProductStatus__c, Month))/1000000),'####')

Thanks

AS

sunny_talwar

Using that expression, I get this chart

Capture.PNG

But this is not the same as the one you have currently

amit_saini
Master III
Master III
Author

Please select below option:

In my attachment by mistake I have selected "Accumulate".

Thanks,

AS 

amit_saini
Master III
Master III
Author

Attached is correct sample.

Kushal_Chawda

take month as dimension and below expression

=rangesum(aggr(above( sum( aggr(sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate), CurrencyIsoCode, Month))/1000000,0,RowNo()),Month))

Kushal_Chawda

see this

Dimension: month

Expression:

rangesum(aggr(above( sum( aggr(sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate), CurrencyIsoCode, Month))/1000000,0,RowNo()),Month))

Capture.JPG

amit_saini
Master III
Master III
Author

This I can achieve by simply selecting below option :

But it's mandatory to call 2 dim and when we are calling this I'm not getting expected output.

Thanks,

AS

sunny_talwar

May be this

Capture.PNG

=Sum(Aggr(RangeSum(Above(sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate)/1000000, 0, RowNo())), UP2GO_2F__pkl_ProductStatus__c, MonthNum))

Added this to your script

TempTable:

LOAD Month

Resident Deliverable;

Left Join (TempTable)

LOAD UP2GO_2F__pkl_ProductStatus__c

Resident Deliverable;

Concatenate(Deliverable)

LOAD *

Resident TempTable;

MonthSort:

LOAD Month,

  Num(Month) as MonthNum

Resident Deliverable

Order By Month;

DROP Table TempTable;

sunny_talwar

Slightly modify your script to this

TempTable:

LOAD DISTINCT Month

Resident Deliverable;

Left Join (TempTable)

LOAD DISTINCT UP2GO_2F__pkl_ProductStatus__c

Resident Deliverable;

Concatenate(Deliverable)

LOAD *

Resident TempTable;

MonthSort:

LOAD DISTINCT Month,

  Num(Month) as MonthNum

Resident Deliverable

Order By Month;

DROP Table TempTable;