Duplicate Date time values in X-axis

[shaan007]

I am having another strange problem here.

This is a List box of Transaction Date and time.

It’s not supposed to show duplicate values.

That’s mean those two dates are not the same.

Do u have any Idea?

please see my attachment image.I made that Date and time field from

TransactionALL:

LOAD
PK_TRANSACTION,
[Trans Start DateTime],
[Trans End DateTime]
FROM
TransactionALL.qvd
(qvd);

TransactionFlow:

load DISTINCT
PK_TRANSACTION
,TimeStamp(Num([Trans Start DateTime])+(((IterNo()-1)/60/24)),'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]
,1 as [TransCont Count]
Resident Transaction
While (Num([Trans Start DateTime])+(((IterNo()-1)/60/24))) < (Num([Trans End DateTime]))
;

[Anonymous]

try with distinct keyword ! hope it will help you

[shaan007]

Thank you for your reply. I did try that

[Colin-Albert]

you need to round your timestamp values to the minute. The expression you have used is formatting the text representation of the timestamp but is not rounding the numeric portion so several different numeric values for timestamp share the same text representation hence you see duplicates.

Floor will round the values down to the nearest minute

Try using this to round down to the nearest minute

TimeStamp(floor(num([Trans Start DateTime])+(((IterNo()-1)/60/24)), 1/(24 *60)),'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]

This will round down to the nearest 15 minutes

TimeStamp(floor(num([Trans Start DateTime])+(((IterNo()-1)/60/24)), 15/(24 *60)),'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]

[shaan007]

Thank you For the reply Collin.

Floor didnt help.

it still create more fluctuation in the chart.

Please refer the correct chart image in my attachment.

NOTE:

I tried this code listed below ..WORKED!!! still one problem,....

TimeStamp#(Num([Trans Start DateTime])+(((IterNo()-1)/60/24))'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]

this giving me correct chart but I cannot view the normal  date time . its all in Numeric value.

when I try this

TimeStamp(TimeStamp#(Num([Trans Start DateTime])+(((IterNo()-1)/60/24)),'D/MM/YYYY h:mm:ss TT'),'D/MM/YYYY h:mm:ss TT')

I see nothing in the chart.

i have no idea whats causing the problem.

[Colin-Albert]

Try this

Timestamp(TimeStamp#(Num([Trans Start DateTime])+(((IterNo()-1)/60/24))'D/MM/YYYY h:mm:ss TT')) as [TransCont DateTime]

Timestamp#() converts text to a timestamp value using the default or specified format for the input text

Timestamp() formats a numeric value using the default timestamp format or the specified format for the output data

[shaan007]

Thank you Collin.

I have already tried that Code-  Timestamp(TimeStamp#(Num....

its so strange. Its giving Null value

[Colin-Albert]

Can you post some example values for Num([Trans Start DateTime])

[Colin-Albert]

This should work

timestamp ( floor( (num( Start DateTime]) + ((IterNo()-1)/60/24) ), 1/(24 * 60) ))  

[jonathandienst]

If I understand you correctly, you want times rounded to the nearest minute. This should do it:

TimeStamp(Round([Trans Start DateTime] + (IterNo()-1)/60/24, 1/60/24), 'D/MM/YYYY h:mm:ss TT')

HTH

Jonathan