I am very new to web intelligence and i have a set of data coming through in this format
dd/mm/yyyy hh:mm
What i need to be able to do is create a time bucket as i could have over 30 items within a specific hour, then i can sum the total of that information within the hour
i.e if its 13:21 then i can put that into time bucket 13
How would i do or build this in either the quer or variable section, if thats even the right place to do that?
Anyones help would be very beneficial as i have no training on the BI, although i am familiar with qlikview and Power BI, so the script and query process/design i have some logic around
As the format of your variable is fixed, then you can create your time bucket using this formula:
Time bucket =Left([your date object here];13)
That should give you a result of ‘dd/MM/yyyy hh’ for each date/time point allowing you to roll up the results into the various buckets.
However, note that you probably won’t have a complete 24 hour set for each date.
Also, before you create the bucket, you may want to reformat the date/time object into ‘yyyy/MM/dd hh:mm’ so everything sorts correctly. (Note that month is MM not mm - which is minutes.)
To do that, modify the above formula like this:
=Left(FormatDate([your date object here];"yyyy/MM/dd hh:mm");13))
If you don’t need a set of buckets for each date, then use
(BOB member since 2009-01-09)