Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Greeting for the day. I'm facing challenge while taking into account date difference, System date and recorded data in the data. I'm looking for date difference = system date - recorded data.
I apply following algorithm;
date(Now(), 'hh') -Hour(Time([Last Occurrence Time], 'HH:MM:SS')
The above algorithm is giving me the difference, but it not correct.
For e.g. if I have recorded date (22/10/2020 3:36:00 PM) and system date (28/10/2020 12:55:00 PM) then I should have answer 2.43 instead of that I'm getting answer 5.87 . I'm looking for the difference irrespective of data, only time should be considered.
Also, as another example, when recorded date (9/24/2020 11:56:00 AM) then I'm getting answer 33.86 instead of 2.58 .
Can somebody explain me, how it is calculated?
Am I making some mistake in formula?
Thanking you in anticipation.
Regards,
Sagar.
If you need the interval between two timestamps you could just subtract them like this.
Interval(Now()-[Last Occurrence Time], 'HH:MM:SS')
Hi @Sagar082 ,
You can bring 2 field in script by convert the Timestamp to Time and subtract in front end.
For ex:
Load *, Time(DateTime , 'HH.mm') as T1, Time(DateTime1, 'HH.mm') as T2 Inline
[
DateTime, DateTime1
22/10/2020 3:36:00 PM,28/10/2020 12:55:00 PM
];
In front end, you can use like below
=Time(T1 - T2, 'HH.mm')
Hi Vegar,
Greeting for the day. Firstly, thank for the solution, but it did not worked for me. As, the interval will take into account date part, which I'm not looking for.
But, still thanks as I am novice to qliksense. I came across available function Interval. It may help me in future.
I will post the derived solution by me, for my specific need soon..
Have a nice day ahead.
Regards,
Sagar.
Hi Mayil,
Greeting for the day. Thank you for solution. You're solution is pretty close to one which I derived.
Have a nice day ahead.
Regards,
Sagar.
The solution which I derived was as follows;
Time(date(now()), 'hh:mm') - time([var],'hh:mm'), 'hh')
The purpose was to capture the system time and get the difference on the recorded time, irrespective of date as that will be taken care in another logic.
Interval was good option, but it mislead as it take into account date part too.