topic Re: Cumulative Sum Help! in QlikView

Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 09:44:22 GMT

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,

Re: Cumulative Sum Help!

kaushiknsolanki — Fri, 30 Jun 2017 09:48:47 GMT

Hi,

Check this.

Rangesum cumulative with 2 dimensions | Qlik Community

If doesnt help share your application.

Regards,

Kaushik Solanki

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 09:52:48 GMT

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,

Re: Cumulative Sum Help!

Kushal_Chawda — Fri, 30 Jun 2017 09:58:04 GMT

maybe try

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

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 10:04:55 GMT

Sorry Kush not working.

Attached is sample.

Thanks,

Re: Cumulative Sum Help!

Kushal_Chawda — Fri, 30 Jun 2017 10:37:52 GMT

try this

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

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 11:31:40 GMT

Sorry not working!

Thanks,

Re: Cumulative Sum Help!

sunny_talwar — Fri, 30 Jun 2017 11:36:10 GMT

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!

amit_saini — Fri, 30 Jun 2017 11:45:01 GMT

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,

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 11:47:01 GMT

Sorry This is Current Senario:

And Output in above is comment is required.

Thanks,

Re: Cumulative Sum Help!

sunny_talwar — Fri, 30 Jun 2017 11:57:53 GMT

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

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 12:00:41 GMT

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

Re: Cumulative Sum Help!

sunny_talwar — Fri, 30 Jun 2017 12:02:23 GMT

Using that expression, I get this chart

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

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 12:06:00 GMT

Please select below option:

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

Thanks,

Re: Cumulative Sum Help!

amit_saini — Fri, 30 Jun 2017 12:09:27 GMT

Attached is correct sample.

Re: Cumulative Sum Help!

Kushal_Chawda — Fri, 30 Jun 2017 12:19:53 GMT

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!

Kushal_Chawda — Fri, 30 Jun 2017 12:22:30 GMT

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!

amit_saini — Fri, 30 Jun 2017 12:26:25 GMT

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,

Re: Cumulative Sum Help!

sunny_talwar — Fri, 30 Jun 2017 12:53:32 GMT

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!

sunny_talwar — Fri, 30 Jun 2017 13:05:19 GMT

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;