Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sunainapawar
Creator
Creator

How to take cumulative of a particular expression

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

6 Replies
tresesco
MVP
MVP

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
Creator
Creator
Author

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

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
Creator
Creator
Author

Hi Tresesco,

I am sharing a sample table  below:-

 

Sales officeFMonthMarket Share GrowthCumulative
Bihar officeApr1.95%1.95%
Bihar officeMay-0.30%1.65%
Bihar officeJun-3.59%-1.93%
Bihar officeJul5.47%3.54%
Delhi   Haryana officeApr4.31%7.86%
Delhi   Haryana officeMay7.20%15.06%
Delhi   Haryana officeJun-6.24%8.82%
Delhi   Haryana officeJul-4.28%4.53%
Gujarat officeApr13.29%17.82%
Gujarat officeMay-3.27%14.55%
Gujarat officeJun0.90%15.44%
Gujarat officeJul1.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
Creator
Creator
Author

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

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