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.

       

      Sum({<[o-date]={'$(=Date(Today()-363))'}>}[NetSales])

       

      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

          Maybe

           

          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,

                  thanks

                  Sasi


              • 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

                  Hi,

                   

                  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:

                   

                  Sum({<SalesTimestamp={"<=$(=Now())"}>}[NetSales])


                  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 !