topic Re: How Could I do this? with calculated dimension in QlikView

How Could I do this? with calculated dimension

ecabanas — Thu, 09 May 2013 08:40:02 GMT

I wanna calculate the number of saleslines that has different profits

Example:

Calculated dimension (sale profit) Number of orders

less than 0,15 3

0,15-0,29 4

More than 0,29 5

The formula to calculate the sale profit:

sum({<ESTADO_PEDIDO={'Facturado'},CalendarMonthAndYear=, [Codventa]={'PV0***'}>} IMPORTBRUT)

sum({<ESTADO_PEDIDO={'Facturado'},CalendarMonthAndYear=, [Codventa]={'PV0***'}>} Cost)

sum({<ESTADO_PEDIDO={'Facturado'},CalendarMonthAndYear=, [Codventa]={'PV0***'}>} CAMPAÑA)

To count the number of orders:

Count(DISTINCT{<CalendarMonthAndYear=,ESTADO_PEDIDO={'Facturado'}, [Codventa]={'PV0***'}>} Codventa)

Thank's

Re: How Could I do this? with calculated dimension

Gysbert_Wassenaar — Thu, 09 May 2013 08:57:20 GMT

Maybe something like this:

if( aggr(sum({<ESTADO_PEDIDO={'Facturado'},CalendarMonthAndYear=, [Codventa]={'PV0***'}>} rangesum(IMPORTBRUT,Cost,CAMPAÑA)),Codventa) < 0.15
, dual('less than 0,15',1),

if( aggr(sum({<ESTADO_PEDIDO={'Facturado'},CalendarMonthAndYear=, [Codventa]={'PV0***'}>} rangesum(IMPORTBRUT,Cost,CAMPAÑA)),Codventa) <= 0.29, dual('0,15-0,29',2),dual('more than 0,29',3)))

Re: How Could I do this? with calculated dimension

ecabanas — Thu, 09 May 2013 09:23:42 GMT

Hi Gysbert,

The expression gave me an error and I don't know why?

thank's

=if( aggr(sum({<ESTADO_PEDIDO={'Facturado'},CalendarMonthAndYear=, [Codventa]={'PV0***'}>} rangesum(IMPORTBRUT,Cost,CAMPAÑA)),Codventa) < 0,29, dual('less than 0,29',1),dual('more than 0,29',2))

Re: How Could I do this? with calculated dimension

Gysbert_Wassenaar — Thu, 09 May 2013 10:00:00 GMT

Your numbers use a comma as decimal separator: ...nta) < 0,29, dual(... The extra comma mucks up the expressions. You could try using quotes around it "0,29"

Re: How Could I do this? with calculated dimension

ecabanas — Thu, 09 May 2013 10:05:23 GMT

BRILLIANT!!!!

many thank's!!!