Splitting down the day

[#1]

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.

[swuehl]

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.

[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])

[#4]

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

[sasiparupudi1]

It would be easy to filter if you have a timestamp may be like [ainput-datetime]..

[swuehl]

You can also do a similar search on ainput-date:

{<

[o-date]={'$(=Date(Today()-363))'},

[ainput-time] = {"<=$(=Time(Now(1)))"},

[ainput-date] = {"<=$(=Date(now(1)))"}

>}

[Anonymous]

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!

[#8]

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

[swuehl]

If date and time already show a numeric representation, it should be enough to write:

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

[sasiparupudi1]

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