How to take cumulative of a particular expression

[sunainapawar]

Hello,

I am trying to take cumulative of below expression by using rangesum function, and my dimension is a drill down value.(sales office, District)

=(Sum({<FYear=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales)
/
Sum({<FYear=,company_name=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales)
)
-
(
sum({<FYear=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales)
/
sum( {<FYear=,company_name=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales)
)

using rangesum:-

RangeSum(Above(
(Sum({<FYear=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales)
/
Sum({<FYear=,company_name=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales) ),0,RowNo(TOTAL)))
-
RangeSum(Above(
sum({<FYear=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales)
/
sum( {<FYear=,company_name=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales),0,RowNo(TOTAL)))

But I am not getting proper value, cumulative data which I am getting is adding up off all sales office all together.

But  for one sales office, it should add for that particular sales office only.

Please suggest

[tresesco]

The latest 2020 Feb version has this feature in-built for which you might not have to write expression for accumulation. Check here: https://www.youtube.com/watch?v=jfDOIUDC__g

[sunainapawar]

Hi Tresesco,

We are not upgrading our qik environment as of now, can you please look into below expression if any changes is required.

We have to achieve this by using expression only.

Regards

[tresesco]

It becomes easier to work on a sample-data-app. Try to share a sample app and explain your expected output in the same context.

[sunainapawar]

Hi Tresesco,

I am sharing a sample table  below:-

Sales office	FMonth	Market Share Growth	Cumulative
Bihar office	Apr	1.95%	1.95%
Bihar office	May	-0.30%	1.65%
Bihar office	Jun	-3.59%	-1.93%
Bihar office	Jul	5.47%	3.54%
Delhi   Haryana office	Apr	4.31%	7.86%
Delhi   Haryana office	May	7.20%	15.06%
Delhi   Haryana office	Jun	-6.24%	8.82%
Delhi   Haryana office	Jul	-4.28%	4.53%
Gujarat office	Apr	13.29%	17.82%
Gujarat office	May	-3.27%	14.55%
Gujarat office	Jun	0.90%	15.44%
Gujarat office	Jul	1.69%	17.13%

If you check for 'Bihar office', in cumulative column, it's perfectly adding the market share growth till July month,

but if you check for 'Delhi   Haryana office', the cumulative value should start from '4.31%', and based on that it should give cumulative for that particular sales office.

But in my scenario, it's just  keep on adding all above values irrespective of different sales offices.

I am trying to achieve this in line chart with below expression:-

(RangeSum(Above(total
(Sum({<FYear=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales)
/
Sum({<FYear=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales) ),0,RowNo(TOTAL))))
-
(RangeSum(Above(total
sum({<FYear=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales)
/
sum( {<FYear=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales),0,RowNo(TOTAL))))

Please suggest

[sunainapawar]

Hello,

I tried with below expression in a table, without using TOTAl , this is giving me correct result. Delhi Haryana Office is starting from 4.3%, but when we use same in Line chart, its getting added up. I tried to convert it to combo chart and sorted it, but in combo its considering only one Sales office. 

RangeSum(Above(
(Sum({<FYear=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales)
/
Sum({<FYear=,company_name=,Date={">=$(=YearStart(max(Date),0,4))<=$(=AddYears(MonthEnd(max(Date)),0))"}>}sales) ),0,RowNo()))

-
RangeSum(Above(
(sum({<FYear=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales)
/
sum( {<FYear=,company_name=,Date={">=$(=YearStart(max(Date),-1,4))<=$(=AddYears(MonthEnd(max(Date)),-1))"}>}sales)),0,RowNo()))

I have attached the screenshot of output.

Please suggest.

[tresesco]

As I said, a sample app would help. But if you can't share one, have a look here to get some idea : https://community.qlik.com/t5/New-to-QlikView/Change-in-Sales/m-p/1679296#M386874