topic Re: Values not being grouped on dimension in QlikView

Values not being grouped on dimension

Fri, 11 Apr 2014 14:22:33 GMT

Why would the dimension values on my chart not be being grouped together fully?

I have a very simple setup, with one data table and one calendar table with one common date field

Re: Values not being grouped on dimension

Roop — Fri, 11 Apr 2014 14:27:33 GMT

I need more information.

is it the sort order that is incorrect?

Thanks

Re: Values not being grouped on dimension

whiteline — Fri, 11 Apr 2014 14:28:07 GMT

Hi.

I guess because there is a time part in each period.

So numeric representation of each value is not the same.

Use floor() function to omit the time part:

=Date(floor(Period), 'DD/MMM/YY')

Re: Values not being grouped on dimension

Anonymous — Fri, 11 Apr 2014 14:29:22 GMT

I suspect that your date dimension is actually timestamp (date + time) formatted as date. Try to fix it in the script using

date(floor("date field")) as "date field"

It will remove the time part.

Re: Values not being grouped on dimension

Roop — Fri, 11 Apr 2014 14:32:20 GMT

It appears as though your date field also contains the time (although it is not possible to see it). Change the value to a date excluding time using floor.

Re: Values not being grouped on dimension

Fri, 11 Apr 2014 14:41:34 GMT

Awesome. Thanks guys

I'm a bit of a beginner at this and was tearing my hair out!

All working now

Re: Values not being grouped on dimension

MarcoWedel — Fri, 11 Apr 2014 15:24:22 GMT

Hi Alex,

other methods (calculated dimensions):

=Date#(Date(Timestampfield, 'DD/MMM/YY'), 'DD/MMM/YY')

=MakeDate(Year(Timestampfield), Month(Timestampfield), Day(Timestampfield))

=Date#(Left(Timestampfield, 9), 'DD/MMM/YY')

regards

Marco