topic Re: Split time-stamp into date and time in QlikView

Split time-stamp into date and time

Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I have a column in the format (DD MMM hh:mm for example 23 Aug 10:30). How could I calculate the number of days between today and say this date (i.e 31 Aug - 23 Aug = 8). I have tried format the time-stamp with no success.

Please advise.

Thanks

Re: Split time-stamp into date and time

sunny_talwar — Wed, 31 Aug 2016 09:39:12 GMT

You can try this:

LOAD Today() - SetDateYear(DateField, Year(DateField)) as DayDiff

LOAD Today() - SetDateYear(TimeStamp(DateField, 'DD MMM hh:mm'), Year(DateField)) as DayDiff

Re: Split time-stamp into date and time

Anonymous — Wed, 31 Aug 2016 09:49:46 GMT

Hi Pravir,

Try with this:

Interval(Today()-Floor(SetDateYear(DateField,Year(DateField)),'DD') as DayDiff

It will return your difference on days, if you want to change it to months, change 'DD' with 'MM'.

Regards!

Re: Split time-stamp into date and time

sunny_talwar — Wed, 31 Aug 2016 09:52:19 GMT

I don't think interval can return MM, for calculating month difference you will need something more complicated like this:

How Do I Get the Number of Months Between Two Dates?

Re: Split time-stamp into date and time

Wed, 31 Aug 2016 11:19:09 GMT

Hi Gents,

I have resolved the issue. The time-stamp didn't have a year so I added that in using another function.

While we are here, I am also struggling to highlight certain rows of data in my table. Say for example I wanted QlikView to automatically highlight specific rows in red where the difference between today's date and the time-stamp data points exceeded a certain value (i.e 31 Aug - 23 Aug = 8 etc). What could a possible solution for this be?

Thanks for all your help guys.

Kind Regards,

Pravir

Re: Split time-stamp into date and time

Anonymous — Wed, 31 Aug 2016 11:58:00 GMT

Hi Pravir, you can use two ways, visual cues on table options, or edit the Background colour with a personal expression.

Regards!

Re: Split time-stamp into date and time

Wed, 31 Aug 2016 12:16:46 GMT

Hi Manuel,

Thanks for your reply.

Visual Cues doesn't seem to be working. Tried using it earlier, it only highlights the 1 cell of the expression I am conditioning on. Is there a way to highlight the entire row based on a single value in that row. How would I edit the background colour?

Kind Regards,

Pravir

Re: Split time-stamp into date and time

Wed, 31 Aug 2016 13:01:07 GMT

Thanks guys I have (by accident) solved the problem to my own question.

I have resolved the issue.

Thanks for the help

Regards,

Pravir

Re: Split time-stamp into date and time

Anonymous — Thu, 01 Sep 2016 06:10:07 GMT

Hi pravir,

On expressions and dimensions you can edit the expression of background colour. Don't use ARGB function or colour functions with alpha values, because Excel don't undertand alpha colours, use RGB function. You have to edit all background expressions for the rows.

Regards!

Re: Split time-stamp into date and time

vardhancse — Thu, 01 Sep 2016 06:11:45 GMT

INTERVAL(DATE('01/05/2016','DD/MM/YYYY')-DATE([DATE],'DD/MM/YYYY'),'D') as AGEING,