topic Re: Cumulative sum in QlikView

Cumulative sum

manu1512 — Mon, 26 Nov 2018 10:19:53 GMT

Hi all,

I am using rangesum for the counting the expression in trend chart.

rangesum(above(Sum({<Equipment_isSMA_Upgrade_Eligible={1},Flag={2},MonthYear=,QuarterYear=>}if(EquipmentStartYear<= Year,EquipmentCount)),0,RowNo())))

but when i am selecting any year the values are getting changed ....which is not required by business.

Can anyone help me in finding the alternate solution for this?

Re: Cumulative sum

Thiago_Justen_ — Mon, 26 Nov 2018 11:04:52 GMT

What about this?

rangesum(above(Sum({<Equipment_isSMA_Upgrade_Eligible={1},Flag={2},MonthYear=,QuarterYear=,"EquipmentStartYear"={"<=Year"}>}EquipmentCount),0,RowNo())))

Re: Cumulative sum

manu1512 — Mon, 26 Nov 2018 11:19:57 GMT

sorry its showing no data display

Re: Cumulative sum

sunny_talwar — Mon, 26 Nov 2018 12:34:32 GMT

May be try this

RangeSum(Above(
Sum({<Equipment_isSMA_Upgrade_Eligible = {1}, Flag = {2}, MonthYear, QuarterYear, Year>}
   If(EquipmentStartYear <= Year, EquipmentCount))
, 0, RowNo())))

or this

RangeSum(Above(
Sum({<Equipment_isSMA_Upgrade_Eligible = {1}, Flag = {2}, MonthYear, QuarterYear, Year>}
   If(EquipmentStartYear <= Year, EquipmentCount))
, 0, RowNo())))
*
Avg({<Equipment_isSMA_Upgrade_Eligible = {1}, Flag = {2}>} 1)

Re: Cumulative sum

manu1512 — Mon, 26 Nov 2018 13:00:27 GMT

Thanks @sunny_talwar its worked second one but now i am facing only one issue that in x axis previously year was shown up to 2025 now it reduced to 2018

Re: Cumulative sum

sunny_talwar — Mon, 26 Nov 2018 13:05:24 GMT

This happens when you select a 2018, right? You want to see all the years starting from the minimum year selected?

Re: Cumulative sum

sunny_talwar — Mon, 26 Nov 2018 13:08:28 GMT

You can try this

RangeSum(Above(
Sum({<Equipment_isSMA_Upgrade_Eligible = {1}, Flag = {2}, MonthYear, QuarterYear, Year>}
   If(EquipmentStartYear <= Year, EquipmentCount))
, 0, RowNo())))
*
Avg({<Equipment_isSMA_Upgrade_Eligible = {1}, Flag = {2}, MonthYear, QuarterYear, Year = {"$(='<=' & Min(Year))"}>} 1)

Re: Cumulative sum

manu1512 — Mon, 26 Nov 2018 13:21:51 GMT

Hello @sunny_talwar

Many thanks for help but unfortunately it is showing no data to display 😞 😞

Re: Cumulative sum

manu1512 — Mon, 26 Nov 2018 13:22:26 GMT

No this happens when i dont select any year

Re: Cumulative sum

sunny_talwar — Mon, 26 Nov 2018 13:24:17 GMT

@manu1512 wrote:
No this happens when i dont select any year

Try this and see if this makes any difference

RangeSum(Above(
Sum({<Equipment_isSMA_Upgrade_Eligible = {1}, Flag = {2}, MonthYear, QuarterYear, Year>}
   If(EquipmentStartYear <= Year, EquipmentCount))
, 0, RowNo())))
*
Avg(1)

Re: Cumulative sum

manu1512 — Mon, 26 Nov 2018 13:28:31 GMT

sorry it does not make any difference but still many thanks for help

Re: Cumulative sum

manu1512 — Mon, 26 Nov 2018 13:30:00 GMT

IT WORKED Many thanks .......just need to remove flag =2 in average expression