Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

exclude a few data points from one of the few lines in a line chart

Hi there, firstly thanks for your time! This question has being holding off my work for days...

I have a line chart, x-axis is week number, y-axis is insurance take-rate(one line for [fiscal] = 2014, the other for [fiscal] = 2013).

Since the latest [week number] for FY14 is 75, the value of insurance take-rate is not valid for any week with a [week number] larger than 75, and you can see it's all flat when the [week number] is >= 75.

What I need to solve is to exclude those data points where the [week number] is >= 75 AND ONLY FOR [fiscal] = 2014; I don't want to do any change on [fiscal] = 2013 since for 2013, all value is valid.

The data points need to be excluded is shown in the following chart, and I attached the excel & qvw file here.

Please let me know if you have any hints or solutions!


Thanks!

Xuan

2014-02-18_1412.png

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

Maybe:

if([week number] <=75,rangesum(above(total sum({$<fiscal = {'2014'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2014'}>}ID), 0, RowNo())))

View solution in original post

9 Replies
jerem1234
Specialist II
Specialist II

I think you may have forgotten to attach your files . But as a guess, an if statement should do the job.

MK_QSL
MVP
MVP

Something like below

SUM({<Year = {'2013'}>}TakeRate)

&

SUM({<Year = {'2014'}, Week = {'<=75'} >}TakeRate)

Change SUM with your respective Aggr Function

and Year and Week should be as per your field name.

Not applicable
Author

Hi, the expressions I used were:

it's in rangesum function

FY13:

rangesum(above(total sum({$<fiscal = {'2013'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2013'}>}ID), 0, RowNo()))

FY14:

rangesum(above(total sum({$<fiscal = {'2014'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2014'}>}ID), 0, RowNo()))

Not applicable
Author

Hi Jerem, sorry I made a mistake, because it was recovered so the file was not there any more. but

the expressions I used were:

it's in rangesum function

FY13:

rangesum(above(total sum({$<fiscal = {'2013'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2013'}>}ID), 0, RowNo()))

FY14:

rangesum(above(total sum({$<fiscal = {'2014'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2014'}>}ID), 0, RowNo()))

MK_QSL
MVP
MVP

add week as per my above reply and let me know what you got

rangesum(above(total sum({$<fiscal = {'2014'}, Week = {'<=75'}>}insurance_take), 0, RowNo()))

Gysbert_Wassenaar

Click on the + in front of the expression, select Background Color and add an expression to calculate the color. Something like =if( [week number] is >= 75 AND [fiscal] = 2014, white(0))


talk is cheap, supply exceeds demand
Not applicable
Author

hi, it didn't exclude the data points, but only stopped doing the cumulative calculating for weeks >= X.

I did this:

rangesum(above(total sum({$<fiscal = {'2014'}, [week number] = {'<= 50'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2014'}, [week number] = {'<= 50'}>}ID), 0, RowNo()))

and got the chart:

2014-02-18_1455.png

jerem1234
Specialist II
Specialist II

Maybe:

if([week number] <=75,rangesum(above(total sum({$<fiscal = {'2014'}>}insurance_take), 0, RowNo()))

/rangesum(above(total count({$<fiscal = {'2014'}>}ID), 0, RowNo())))

Not applicable
Author

Great, it worked!

Thanks thanks thanks Jerem1234!