topic Re: How to write a Cumulative expression for attached data in QlikView

How to write a Cumulative expression for attached data

kunkumnaveen — Mon, 25 Sep 2017 08:31:04 GMT

Hello All,

I was trying to write a cumulative sum expression but some reason it is giving zero as out put ..what i mean is

For

Calmonth(Field) =201705-----it has to sum 201704+201705

=201706------it has to sum 201704+201705+201706

........So on

Expression i tried is :

RangeSum(Above(sum(VOLUME)), 0, RowNo())

Any suggestion on how to write a cumulative expression for below attached data ..

Re: How to write a Cumulative expression for attached data

Anil_Babu_Samineni — Mon, 25 Sep 2017 08:34:07 GMT

Try this?

Rangesum(Below(Sum(VOLUME),0),Above(VOLUME,1,RowNo(TOTAL)))

Re: How to write a Cumulative expression for attached data

sunny_talwar — Mon, 25 Sep 2017 08:34:50 GMT

Output as zero in a chart or are you doing this in a text box object?

Re: How to write a Cumulative expression for attached data

kunkumnaveen — Mon, 25 Sep 2017 08:41:56 GMT

Hello Anil,thanks for your reply..

I tried your expression what i am getting is

when i tried SUM(volume) i am getting same

I have attached Sample data aswell......

Re: How to write a Cumulative expression for attached data

kunkumnaveen — Mon, 25 Sep 2017 08:43:08 GMT

HI ,i am doing it in Table....

Re: How to write a Cumulative expression for attached data

Anil_Babu_Samineni — Mon, 25 Sep 2017 08:46:38 GMT

Then Try this?

RangeSum(above(TOTAL sum(VOLUME),0,RowNo(TOTAL)))

Re: How to write a Cumulative expression for attached data

kunkumnaveen — Mon, 25 Sep 2017 09:08:33 GMT

Any Suggestion Please

Re: How to write a Cumulative expression for attached data

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:14:17 GMT

Is my second reply won't for you?

Re: How to write a Cumulative expression for attached data

effinty2112 — Mon, 25 Sep 2017 09:32:13 GMT

Hi Naveen,

The brackets in your original expression were wrong, try;

Rangesum(Above(Sum(VOLUME),0, RowNo()))

gives:

CALMONTH	Sum (VOLUME)	Rangesum(Above(Sum(VOLUME),0, RowNo()))
	540620590.72295	0
201704	88254316.243012	88254316.243012
201705	108867027.09298	197121343.33599
201706	142117893.154	339239236.48999
201707	71972231.979963	411211468.46995
201708	105148241.14499	516359709.61495
201709	24260881.108001	540620590.72295
201710	0	540620590.72295
201711	0	540620590.72295
201712	0	540620590.72295
201801	0	540620590.72295
201802	0	540620590.72295
201803	0	540620590.72295

Regards

Andrew

Re: How to write a Cumulative expression for attached data

hari8088 — Mon, 25 Sep 2017 09:32:55 GMT

Use Full accumulation option in expression tab-click on this for particular chart

Re: How to write a Cumulative expression for attached data

PrashantSangle — Mon, 25 Sep 2017 09:33:30 GMT

Hi,

try

Rangesum(Above(sum(VOLUME),0,RowNo(TOTAL)))

Regards,

Re: How to write a Cumulative expression for attached data

kunkumnaveen — Mon, 25 Sep 2017 09:35:44 GMT

Thanks Man its is working but Anil ,is it possible to modify your expression ,i mean your expression is prefect when

if(month<month(today),RangeSum(above(TOTAL sum(VOLUME),0,RowNo(TOTAL))), ? )

if month = month(today),then it should not sum current month value...

i mean current month is September so till august the above expression is prefect and from September to above

201708=sum(201704 to 201708)

201709=sum(201704 to 201708) because month is not lesser than month of today

201710=sum(201704 to 201709)

201711=sum(201704 to 201710)

Re: How to write a Cumulative expression for attached data

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:37:30 GMT

May be possible using this..

if(month<month(TOTAL today()),RangeSum(above(TOTAL sum(VOLUME),0,RowNo(TOTAL))), 'you can initiate anything' )

Re: How to write a Cumulative expression for attached data

PrashantSangle — Mon, 25 Sep 2017 09:40:17 GMT

Hi,

Your logic work.

what do you want to show if month<month(Today()) is not true ????

Regards

Re: How to write a Cumulative expression for attached data

kunkumnaveen — Mon, 25 Sep 2017 10:27:25 GMT

HI,

i am trying to convert

your expression=RangeSum(above(TOTAL sum(VOLUME),0,RowNo(TOTAL)))

in to this

Sum({<CALMONTH ={">=$(=YearStart(Max(CALMONTH))) <=$(=max(CALMONTH))"}>} VOLUME)

but something i am missing ,can you help

Re: How to write a Cumulative expression for attached data

Anil_Babu_Samineni — Mon, 25 Sep 2017 10:29:38 GMT

you can write this

RangeSum(above(TOTAL sum({<CALMONTH ={">=$(=YearStart(Max(CALMONTH))) <=$(=max(CALMONTH))"}>} VOLUME),0,RowNo(TOTAL)))