Calculated Dimension Grouping

[louwriet]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[louwriet]

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

Thank so much for the help

Regards

Louw