topic Re: How to take cumulative of a particular expression in App Development

How to take cumulative of a particular expression

sunainapawar — Thu, 27 Feb 2020 07:35:31 GMT

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

Re: How to take cumulative of a particular expression

tresB — Thu, 27 Feb 2020 07:52:28 GMT

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

Re: How to take cumulative of a particular expression

sunainapawar — Thu, 27 Feb 2020 09:20:46 GMT

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

Re: How to take cumulative of a particular expression

tresB — Thu, 27 Feb 2020 09:52:33 GMT

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.

Re: How to take cumulative of a particular expression

sunainapawar — Thu, 27 Feb 2020 10:17:50 GMT

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

Re: How to take cumulative of a particular expression

sunainapawar — Fri, 28 Feb 2020 05:34:24 GMT

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

Re: How to take cumulative of a particular expression

tresB — Fri, 28 Feb 2020 06:02:43 GMT

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