topic Re: Rangesum expression logic in QlikView

Rangesum expression logic

qlikcheepirishe — Wed, 25 Nov 2020 16:16:04 GMT

I have a bar chart

from date 25 it has 2000 value it bas been added to 26th and 25th,26th data is added to the 27 so ...on now on next month the same process is not getting so what will be the expression to achieve if 1dec has value 500 it should show 500 then in 2nd dec it has 1000 it should show 1500 likewise

below is my expression used in chart

sum(DISTINCT aggr(rangesum(above(TOTAL sum(NET_WEIGHT),0, Rowno())),MONTH_EXCISE_INVOICE_DATE,EXCISE_INVOICE_DATE))

Re: Rangesum expression logic

rubenmarin — Tue, 12 Dec 2017 13:22:15 GMT

Hi Manesh, maybe removing the 'Distinct' clause?

Which value is returning now and which value is expected for 1st and 2nd of the month?

Re: Rangesum expression logic

sunny_talwar — Tue, 12 Dec 2017 13:54:35 GMT

May remove the TOTAL keyword and see if that helps...

Sum(DISTINCT Aggr(RangeSum(Above(Sum(NET_WEIGHT), 0, RowNo())), MONTH_EXCISE_INVOICE_DATE, EXCISE_INVOICE_DATE))

Re: Rangesum expression logic

qlikcheepirishe — Tue, 12 Dec 2017 17:58:18 GMT

Implemented both of your suggestion not working anything else???

Re: Rangesum expression logic

qlikcheepirishe — Tue, 12 Dec 2017 18:00:13 GMT

1st month values what is actually required and I m getting

for 2nd month the expression is required from your expertise...

Re: Rangesum expression logic

qlikcheepirishe — Tue, 12 Dec 2017 18:01:29 GMT

omarbensalem‌ any idea for the requirement.

Re: Rangesum expression logic

MarcoWedel — Tue, 12 Dec 2017 21:02:33 GMT

Hi,

maybe you could try with full accumulation of the chart expression.

In order to restart the accumulation per month you might use the month field as second dimension, stack the second dimension (as one date cannot belong to two months there will only ever be one segment per stack), restrict to a common color and turn off the legend. The result could look like this:

LOAD *,

MonthName(EXCISE_INVOICE_DATE) as MONTH_EXCISE_INVOICE_DATE

INLINE [

EXCISE_INVOICE_DATE, NET_WEIGHT

25/08/2017, 2000

26/08/2017, 4730

27/08/2017, 773

28/08/2017, 3770

29/08/2017, 2318

30/08/2017, 1261

31/08/2017, 1025

01/09/2017, 2000

02/09/2017, 5307

03/09/2017, 2520

];

hope this helps

regards

Marco