23 Replies Latest reply: Jul 6, 2017 12:05 AM by Amit Saini

# 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

• ###### Re: Cumulative Sum Help!

Hi,

Check this.

Rangesum cumulative with 2 dimensions | Qlik Community

If doesnt help share your application.

Regards,

Kaushik Solanki

• ###### Re: Cumulative Sum Help!

Hi Solanki,

I tried this but it's not working:

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

Marked in bold are my 2 dim.

Thanks,

As

• ###### Re: Cumulative Sum Help!

maybe try

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

• ###### Re: Cumulative Sum Help!

Sorry Kush not working.

Attached is sample.

Thanks,

AS

• ###### Re: Cumulative Sum Help!

try this

rangesum(above(total sum(aggr(sum(fcur_PeakAnnualRevenue__c)/sum( distinct Rate), CurrencyIsoCode, Month))/1000000,1,RowNo(TOTAL)))

• ###### Re: Cumulative Sum Help!

Sorry not working!

Thanks,

AS

• ###### Re: Cumulative Sum Help!

What is the output you are expecting to see here? You have UP2GO_2F... and Month as your dimensions, but you aggregating on CurrencyIsoCode and Month? Can you provide what the output needs to look like in numerical terms

• ###### Re: Cumulative Sum Help!

Hi Sunny,

Current :

But Output should be like below Stage values on Bar

 Month Stage Jan \$51.35 Feb \$53.88 Mar \$132.70 Apr \$200.48 May \$202.90 Jun \$327.13 Jul \$487.90 Aug \$580.32 Sep \$733.42 Oct \$993.76 Nov \$1,014.90 Dec \$1,163.88

Thanks,

AS

• ###### Re: Cumulative Sum Help!

Sorry This is Current Senario:

And Output in above is comment is required.

Thanks,

AS

• ###### Re: Cumulative Sum Help!

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

• ###### Re: Cumulative Sum Help!

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

• ###### Re: Cumulative Sum Help!

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

• ###### Re: Cumulative Sum Help!

Using that expression, I get this chart

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

• ###### Re: Cumulative Sum Help!

Please select below option:

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

Thanks,

AS

• ###### Re: Cumulative Sum Help!

Attached is correct sample.

• ###### Re: Cumulative Sum Help!

see this

Dimension: month

Expression:

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

• ###### Re: Cumulative Sum Help!

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

• ###### Re: Cumulative Sum Help!

May be this

=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;

• ###### Re: Cumulative Sum Help!

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;

• ###### Re: Cumulative Sum Help!

Sorry Sunny,

No still it's not working.

The moment you will select year , Cumulative sum gone . See below:

Thanks,

AS

• ###### Re: Cumulative Sum Help!

I guess you will need to fill the missing values

Generating Missing Data In QlikView

• ###### Re: Cumulative Sum Help!

Hi Sunny,

Thank You!

I already got solution to my problem.

This happens, when the second Dimension isn’t completely filled on each month. So with the AsOfTable concept which is explained very good under following link you can build up a nice solution which is easy to handle and give you much more flexibility.

Thanks,

AS

• ###### Re: Cumulative Sum Help!

Hi Sunny,

I am facing some problems with the complex section access.

the requirement is mentioned in the below.

please guide me if you have any idea.

input.

 site state disitnct sales India ap ongole 80 India ap nell 85 india bhp paradise 23 india bhp cellorium 45 us remedy calom 86 us remedy siral 75 kenada kalosa richel 66 kenada kalosa silaj 89 kenada sumera aparlak 25 non-south pradesh kilkut 36 non-south pradesh suraj 25

output for admin.

 site state disitnct sales India ap ongole 80 India ap nell 85 india bhp paradise 23 india bhp cellorium 45 us remedy calom 86 us remedy siral 75 kenada kalosa richel 66 kenada kalosa silaj 89 kenada sumera aparlak 25 non-south pradesh kilkut 36 non-south pradesh suraj 25

out put for who is having india access

 site state disitnct sales India ap ongole 80 India ap nell 85 india bhp paradise 23 india bhp cellorium 45 site2 remedy default 86 site2 remedy default 75 site3 kalosa default 66 site3 kalosa default 89 site3 sumera default 25 non-south pradesh kilkut 36 non-south pradesh suraj 25

out put for  who is having us access

 site state disitnct sales site1 ap default 80 site1 ap default 85 site1 bhp default 23 site1 bhp default 45 us remedy calom 86 us remedy siral 75 site3 kalosa default 66 site3 kalosa default 89 site3 sumera default 25 non-south pradesh kilkut 36 non-south

Thanks,

Ram,

9710463111