11 Replies Latest reply: Aug 26, 2015 4:29 AM by P M RSS

    Splitting down the day

    P M

      I am loading a sales table that has separate date and time columns.

      In my chart I want an expression that brings through the data based on the date AND time up to the minute of the current time.


      e.g. if the time is 15:00 I want all the data up to and including this time on the date I have already specified below.


      This is the expression I am using to identify the correct previous day (it is effectively the equivalent sales day from the previous year) but I need to also include the time criteria as above.




      o-date is the date field  (yyyy-mm-dd)

      NetSales is the sales value


      ainput-time is the entry time (hh:mm)



      Thanks for all your help.

        • Re: Splitting down the day
          Stefan Wühl



          Sum({<[o-date]={'$(=Date(Today()-363))'}, [ainput-time] = {"<=$(=Time(Now(1)))"}>}[NetSales])

          Check the timer modes (argument to time() ) for what is appropriate for your setting. The result of the dollar sign expansion should match the format of your ainput-time field, so you may need to adjust the format, too.

            • Re: Splitting down the day
              Sunny Talwar

              Stefan, shouldn't we just add the formats here just to be safe here:


              Sum({<[o-date]={"$(=Date(Today()-363, 'YYYY-MM-DD'))"}, [ainput-time] = {"<=$(=Time(Now(1), 'hh:mm'))"}>}[NetSales])


              or make the following change in the script:


              SET TimeFormat='hh:mm';

              SET DateFormat='YYYY-MM-DD';


              and then use the expression you have given as is

              Sum({<[o-date]={'$(=Date(Today()-363))'}, [ainput-time] = {"<=$(=Time(Now(1)))"}>}[NetSales])

                • Re: Splitting down the day
                  Sasidhar Parupudi

                  hi sunindia/Swuehl


                  I have a doubt regarding the above expression..Would you mind clarifying it?

                  Sum({<[o-date]={'$(=Date(Today()-363))'}, [ainput-time] = {"<=$(=Time(Now(1)))"}>}[NetSales])

                  will the above expression filter records that are less than the current time for all the days? in other words, are the dates and times are filtered independently?

                  please explain,



              • Re: Splitting down the day
                P M

                Thank you for the great replies, they worked well.

                The results are not not what I expected (they are the correct figures though), I hadn't taken into account the input date.

                Can you help expand the criteria of time please where the combination of:

                [ainput-date] and [ainput-time] are before Now



                Thanks for help

                • Re: Splitting down the day
                  Kim Rørmark



                  Create a new column in your data table containing the timestamp:


                  Timestamp(Timestamp#([o-date] &' ' & [ainput-time], 'yyyy-mm-dd hh:mm')) AS SalesTimestamp


                  Then, use this expression in your object to limit the data to sales from "now" and back:



                  Hope this helps!

                    • Re: Splitting down the day
                      P M

                      Thanks for the replys.


                      The [ainput-date] field is displaying in its own field as 02/04/2013 and [ainput-time] as 16:21

                      When using the line below the new column is displaying as 41366 16:21 and not 2013-04-02 16:21 as I need.

                      What as I doing wrong ?


                      Timestamp(Timestamp#([o-date] &' ' & [ainput-time], 'yyyy-mm-dd hh:mm')) AS SalesTimestamp

                    • Re: Splitting down the day
                      P M

                      I had to tackle this a little differently by loading the date and time separately as numeric values and comparing them against the current date and time in the same manner.

                      I'm not quite sure why I could not do this using date and time but it worked with no additional overhead.


                      Thanks for all help !