Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ugurarslan
Contributor

Range Sum days less than today

Dear all,

I have a line graph, where the line for current month doesn't stop at the day of today, please see below picture. 

Please be informed that I am using the following

Dimension:

day([Reporting Date])

Measurement:

rangesum(above(
Sum({<[Reporting Date]={">=$(=MonthStart(today()))<=$(=today()-4)"},[Cash Book Code]={'CASH'}>}[Cash USD])
,0,RowNo()))

Can you please help me, how do I stop the red line showing data after today?

Thanks

 

Labels (4)
2 Replies
pradosh_thakur
Honored Contributor II

Re: Range Sum days less than today

Try the below

rangesum(above(
Sum({<[Reporting Date]={">=$(=MonthStart(today()))<=$(=today()-4)"},[Cash Book Code]={'CASH'}>}[Cash USD])
,0,RowNo())) * avg({<[Reporting Date]={">=$(=MonthStart(today()))<=$(=today()-4)"}>}1)
Learning never stops.
kaanerisen
Contributor III

Re: Range Sum days less than today

Hi Uğur,

You can limit your calculation by checking if reportdate is less than today or not and you can set the value null for future dates to stop the line at last date you desire.

SampleTable:

load *,
(Rand() * (1.30-0.55 ) + 0.55)*VALUE AS PLANVALUE Inline [
DATE,VALUE
'1.02.2019',7000
'2.02.2019',10000
'3.02.2019',3000
'4.02.2019',5000
'5.02.2019',8000
'6.02.2019',6000
'7.02.2019',9000
'8.02.2019',15000
'9.02.2019',20000
'10.02.2019',3000
'11.02.2019',4000
'12.02.2019',1000
'13.02.2019',6000
'14.02.2019',2400
'15.02.2019',9000
'16.02.2019',1750
'17.02.2019',7500
'18.02.2019',3000
];

Dimension:

DATE

Measure1: 

rangesum(above(Sum(PLANVALUE),0,RowNo()))

Mesaure2 (older dates before less 4 day from today) : 

IF(DATE<=TODAY()-4,
rangesum(above(Sum(VALUE),0,RowNo())),
NULL())

Untitled.png

You can check the attached qvf.

Hope it helps..