topic Re: Splitting down the day in QlikView

Splitting down the day

Wed, 19 Aug 2015 21:53:30 GMT

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

swuehl — Wed, 19 Aug 2015 22:04:55 GMT

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 — Wed, 19 Aug 2015 22:52:25 GMT

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

Thu, 20 Aug 2015 10:12:54 GMT

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

sasiparupudi1 — Thu, 20 Aug 2015 10:27:08 GMT

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

Re: Splitting down the day

swuehl — Thu, 20 Aug 2015 10:59:49 GMT

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)))"}

Re: Splitting down the day

Anonymous — Thu, 20 Aug 2015 11:20:49 GMT

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

Thu, 20 Aug 2015 16:54:32 GMT

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

swuehl — Thu, 20 Aug 2015 16:57:58 GMT

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

Re: Splitting down the day

sasiparupudi1 — Thu, 20 Aug 2015 17:56:06 GMT

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

swuehl — Thu, 20 Aug 2015 20:44:20 GMT

Yes the fields are filtered independently.

o-date will be filtered to a single day 363 days ago, and ainput-time to values below the current time.

The result should be (as of now) 2014-08-22, 00:00:00 to 22:44:00

Re: Splitting down the day

Wed, 26 Aug 2015 08:29:25 GMT

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 !