topic Re: Doubts about expression/days in QlikView

Doubts about expression/days

Fri, 24 Oct 2014 18:15:20 GMT

Hi everybody: I have a little question, i dont know what function or what to do in this case.

I have to calculate a quantity. I have a lapse of time this is a difference between when something is ordered and delivered. This is final date - initial date. For example 1 day or 5 days or 20. In the graphic i have to divide the quantity in the numbers of the diference . I dont know how to do this with the number of days.

1 - 15, 15-30, etc

Sory for my poor english, i´ll put the needed graphic.

In the dimension i´ve put =DAY(DATE((FechaFin - Fecha), 'YY/MM/DD')) . I see the dates, but i´d like to group them.

Re: Doubts about expression/days

JonnyPoole — Fri, 24 Oct 2014 18:24:02 GMT

Hi there,

i suggest using the class() function with your Final Date - Initial Date to create the dimension.

Class ( [Final Date] - [Start Date] , 15)

In this example 15 is the range for each bucket. This will create 'buckets' (like country, person etc...) within which your expression will breakdown into.

Looking at the chart, your expression would be just something like this: sum(Quantity)

Re: Doubts about expression/days

maxgro — Fri, 24 Oct 2014 18:24:08 GMT

maybe class?

see attachment

Re: Doubts about expression/days

MarcoWedel — Fri, 24 Oct 2014 18:26:00 GMT

=Class(FechaFin-Fecha, 15)

as dimension

Re: Doubts about expression/days

Fri, 24 Oct 2014 18:27:08 GMT

Thank you. Can i stop with the buckets in 60 for example?

I mean 0 - 15, 15-30, 30-45, 45-60 and 60+

Re: Doubts about expression/days

Fri, 24 Oct 2014 18:27:54 GMT

Thank you. Can i stop with the buckets in 60 for example?

I mean 0 - 15, 15-30, 30-45, 45-60 and 60+

Re: Doubts about expression/days

JonnyPoole — Fri, 24 Oct 2014 18:33:36 GMT

Hi Jeremias - the function is dynamic. The 60+ bracket will automatically show as 60-75. If you have values over 75 those will be in new classes.

To avoid that use an IF() like this

IF ( [Final Date] - [Start Date] < 60, Class( [Final Date] - [Start Date] , 15) , '60+')

Re: Doubts about expression/days

Fri, 24 Oct 2014 18:39:33 GMT

Yeah, that works. Thank you.

Only a last question. How i change the 0 <= x < 15 to '0 to 15'

Cheers

Re: Doubts about expression/days

JonnyPoole — Fri, 24 Oct 2014 18:44:10 GMT

sure you can purge the <>= signs and replace x with 'to' as follows;

IF ( [Final Date] - [Start Date] < 60, PurgeChar( Class( [Final Date] - [Start Date] , 15,'to'),'>=<') , '60+')

Re: Doubts about expression/days

Fri, 24 Oct 2014 18:50:08 GMT

Thank you! Sory i have two more little questions.

I have negative values in the dates, i think that is an error in the data and that´s not my problem but i don´t wanna show this. How can i hide this values ? i have -15 - 0 . And i have values "-" i think they are nulls. How can i hide this values too?

Re: Doubts about expression/days

JonnyPoole — Fri, 24 Oct 2014 18:57:09 GMT

I think some IF statements to make sure its 0-60 or over 60 would help:

IF ( [Final Date] - [Start Date] >= 0 and [Final Date] - [Start Date] <= 60,

PurgeChar( Class( [Final Date] - [Start Date] , 15,'to'),'>=<') ,

IF( [Final Date] - [Start Date] > 60, '60+')

)

Re: Doubts about expression/days

Fri, 24 Oct 2014 18:59:41 GMT

Thank you.

You helped me a lot.

Cheers!

Re: Doubts about expression/days

JonnyPoole — Fri, 24 Oct 2014 19:00:42 GMT

I thought i saw a question about sorting.

On the sort tab, sort by the calculated dimension and select 'expression'. In the expression try type

min([Final Date] - [Start Date])

Re: Re: Doubts about expression/days

MarcoWedel — Fri, 24 Oct 2014 20:36:01 GMT

Hi,

another solution could be:

=If(FechaFin-Fecha<60,Dual(Replace(Class(FechaFin-Fecha,15),'<= x <','to'),Class(FechaFin-Fecha,15)),Dual('60+',60))

This way you don't have to care about special sorting expressions:

hope this helps also

regards

Marco

Re: Doubts about expression/days

Mon, 27 Oct 2014 12:48:25 GMT

That´s very helpful too.

Thank you, Marco.

Greetings.

Re: Re: Doubts about expression/days

MarcoWedel — Mon, 27 Oct 2014 15:53:33 GMT

You're welcome

regards

Marco