Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe:
if([week number] <=75,rangesum(above(total sum({$<fiscal = {'2014'}>}insurance_take), 0, RowNo()))
/rangesum(above(total count({$<fiscal = {'2014'}>}ID), 0, RowNo())))
I think you may have forgotten to attach your files . But as a guess, an if statement should do the job.
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.
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()))
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()))
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()))
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))
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:
Maybe:
if([week number] <=75,rangesum(above(total sum({$<fiscal = {'2014'}>}insurance_take), 0, RowNo()))
/rangesum(above(total count({$<fiscal = {'2014'}>}ID), 0, RowNo())))
Great, it worked!
Thanks thanks thanks Jerem1234!