Chart Accumulation by expression

[#1]

Hi everyone.

I have chart, and i have to make 6 columns (“100+%”, “50-100%”, “0-50%”, "-50-0%", "-100-50%", "-100%")  instead of current columns.

Is it possible? Can somebody helps me?

This is defenition of my expression::

Count(aggr(Round(

(Sum({$<PositionHistoryDate = {'$(vToDate)'}, OpenDate ={"<=$(vToDate)"}, CloseDate ={">=$(vFromDate)"}>}PositionHistoryValue) /

Sum({$<PositionHistoryDate = {'$(vFromDate)'},OpenDate ={"<=$(vToDate)"}, CloseDate ={">=$(vFromDate)"}>}PositionHistoryValue) - 1 )*100, 10)

,PositionId))

Thanks.

[hic · Accepted solution]

You can define your dimension using e.g.

     Class(Number,50) as Class

(Given that "Number" is the number you today use on the x-axis). You can do this either in the script or as calculated dimension.

Further, if you want to lump all values at the low end (and high end) together, you can combine the above with an If() function:

     If(Number<-100,'-100%',

     If(Number>100,'100+%',

     Class(Number,50))) as Class

HIC

[#3]

Thank you, Henric. It helped me.

Please, can you help me with one more issue?

Can  i convert -100 <= x< -50 to -100 - (-50) or (-100,-50)

02.18.2014-14.07.34 - R.Chauskin's library

[hic]

You could do a

  If(Number<-100,Dual('--100',-125),

    If(Number>100,Dual('100++',125),

    Dual('(' & Num(Class(Number,50)) & ',' & Num(Class(Number,50)+50) & ')',Class(Number,50)+25)

    )) as Class

This needs some explanation... The Dual() function pairs together a text string with a number, which is useful when you want to sort these classes. For instance, the text '--100' is associated with the number -125 (first line of the code). The Class() function returns a dual value, and I use the Dual function to redefine -100 <= x < -50 to (-100,-50).

HIC

[#5]

Henric, Can you help me again, please?

There is  some issue with my chart. I used all you previous advices, and got good result, BUT

my bars collects positions with wrong values. Value differs by 5 from normal.

For example, bar (0,50) accumulates positions with value from -5 to 45.

Can you help me, please?

Screenshots:

02.28.2014-16.32.24 - R.Chauskin's library

02.28.2014-16.37.39 - R.Chauskin's library

[hic]

What expression do you use? (The one in my previous answer does not have this problem - I just verified - so you must have changed it.)

HIC

[#7]

Oh. Sorry. That was problem on my side (with Round() function).

Thank you, Henric. You are really helped me a lot.