topic Dynamic Axis Maximum in QlikView

Dynamic Axis Maximum

Thu, 09 Feb 2012 15:18:06 GMT

I am trying to calculate a dynamic maximum for an axis on a stacked bar chart

What I want is to calculate is the total of Sales Value for cases that are ‘Open’, ‘Won’ and ‘Lost’ by SalesPerson for the current selection(s) and then use the Salesperson with the highest total to set the axis max.

The dimension is SalesPerson

The value required is used for an expression:

sum(if(Status = 'Open' OR Status = 'Won' OR Status = 'Lost', [Sales Value]))

I need to use the above calculation (or set analysis equivalent) to find the Sales person with the highest total. From my investigations I don’t think I can use max() , FirstSortedValue() and I am not sure if I can use aggr() .

Can anyone help?

Regards

Jason

Dynamic Axis Maximum

CELAMBARASAN — Thu, 09 Feb 2012 15:22:19 GMT

Hi,

Use the expression as below in the axis max.

=Sum(TOTAL {<Status={'Open','Won','Lost'}>} [Sales Value])

U can achieve this using set analysis.

Hope it helps

Celambarasan

Dynamic Axis Maximum

v_iyyappan — Thu, 09 Feb 2012 15:28:13 GMT

Hi,

In Chart propertities -> Axes ->Scale ->StaticMax ->Paste this expression

= Sum(TOTAL {<Status={'Open','Won','Lost'}>} [Sales Value])

Regards,

Iyyappan

Re: Dynamic Axis Maximum

Thu, 09 Feb 2012 15:32:00 GMT

Hi Jason,

Barring syntax error...

Following should give you the answer

max(aggr( sum(if(Status = 'Open' OR Status = 'Won' OR Status = 'Lost', [Sales Value])),SalesPerson))

If you have having chart (using the above under Axes will do the trick)

Regards

Dynamic Axis Maximum

Thu, 09 Feb 2012 16:46:54 GMT

Kulbir,

I thought it might be resolved using aggr(), but I couldn't work it out!

Thanks very much for your help.

Jason