topic Re: Chart Accumulation by expression in QlikView

Chart Accumulation by expression

Tue, 18 Feb 2014 08:46:10 GMT

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.

Re: Chart Accumulation by expression

hic — Tue, 18 Feb 2014 09:03:03 GMT

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

Re: Chart Accumulation by expression

Tue, 18 Feb 2014 11:08:59 GMT

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

Re: Chart Accumulation by expression

hic — Tue, 18 Feb 2014 13:04:09 GMT

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

Re: Chart Accumulation by expression

Fri, 28 Feb 2014 13:37:59 GMT

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

Re: Chart Accumulation by expression

hic — Sat, 01 Mar 2014 14:13:51 GMT

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

Re: Chart Accumulation by expression

Mon, 03 Mar 2014 07:31:03 GMT

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

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