topic Re: Calculated Dimension Grouping in QlikView

Calculated Dimension Grouping

louwriet — Sun, 02 Jun 2013 14:33:17 GMT

Hi All,

I would appreciate it if someone can help here.

I came acrros a thrread that show how to strip out the time out of a datetime field. This will work great for me. Attached is the example and my problem market with red.

The problem is i want to show sales per hour, i get a timestamp when the sale is made ddmmyyyy hh:mm:ss. This calculated dimension strip out the hour nicely the only problem is now it shows the hour in duplicates and dont summerize it by the hour. I also need to sort it that it show in the right sort order by starting with first am then to pm

The calculated dimension is:

= Date(date(datetimefield), hh ss)

The calculated dimension show the sales per hour like this for instance:

hour sales

===== =====

6 am 20

6 am 50

8 am 10

9 pm 20

7 pm 11

6 am 10

it should show

hour sales

===== ======

6 am 80

8 am 10

7 pm 11

9 pm 20

Thank so much

Regards

Louw

Re: Calculated Dimension Grouping

Gysbert_Wassenaar — Sun, 02 Jun 2013 18:52:37 GMT

The values in your calculated dimension are still dates, not times. The dates are just formatted as times, but the actual values still include the dates. If you want to group by time you'll have to use something like time(frac(datetimefield),'hh tt'). You can do this in a calculated dimension, but it's probably better to split the datetimefield in the script into a date and a time field.

Re: Calculated Dimension Grouping

louwriet — Mon, 03 Jun 2013 15:57:19 GMT

Hi Gysbert, i did it in my script and it worked 100%

Thank so much for the help

Regards

Louw