Timestamp in Set

[#1]

Hi All,

I'm having a problem with an expression and can't get it right.

I'm trying to select the last cost adjustment record for a selected manufacturing order using

sum({$<TransCode = {'CA'}, [Transaction Timestamp] = {"=max(timestamp#([Transaction Timestamp]))"}>} [Average Cost])

Where CA is the transaction code for cost adjustments there can be only one CA for any given transaction timestamp.

In this example there are two cost adjustments:

The following is the result in a chart which is the sum of the Cost After values. I would have thought that the criteria for Max timestamp would have only returned the last of the transaction set which is 11/09/2011 11:52:02 AM and not the sum of both records.

Hope you can point me in the right direction.

Thanks,

Rich

[swuehl]

Try

sum({$<

TransCode = {'CA'},

[Transaction Timestamp] = {'$(=Timestamp(max({<TransCode = {'CA'} >} timestamp#([Transaction Timestamp]))))'}

>} [Average Cost])

Hope this helps,

Stefan

[#3]

Thanks for the reply Stefan but that expression results in 0.

I converted the my original expression using both the literal timestamp and I get the correct results. As in

sum({$<TransCode = {'CA'}, [Transaction Timestamp] = {'11/09/2011 11:52:02 AM'}>} [Average Cost])

Which leads me to believe the problem lies in

{"=max(timestamp#([Transaction Timestamp]))"}

Any thoughts?

Rich

[vgutkovsky]

Richard, I would tend to agree. First, is your field "Transaction Timestamp" actually stored as a timestamp and not a string? You should make sure that you're reading it in as a timestamp in the script, which will actually assign it an underlying numeric value (much easier to deal with for purposes of set analysis). So your script would look like:

timestamp#([Transaction Timestamp],'MM/DD/YYYY hh:mm:ss TT') as [Transaction Timestamp]

Then your set can be just: {<TransCode = {'CA'}, [Transaction Timestamp] = {$(=max([Transaction Timestamp]))}>}

[Transaction Timestamp]

Regards,
Vlad

[Miguel_Angel_Baeyens]

Hi Richard,

Max() will always return a numeric value, null otherwise. So you will need to represent that number in a Timestamp manner, as Stefan suggested above, like the following:

Sum({$< TransCode = {'CA'}, [Transaction Timestamp] = {"$(=TimeStamp(Max([Transaction Timestamp]), 'MM/DD/YYYY hh:mm:ss TT'))"} >} [Average Cost])

Check for missing values, just in case.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[swuehl]

Thanks for the reply Stefan but that expression results in 0.

I converted the my original expression using both the literal timestamp and I get the correct results. As in

sum({$<TransCode = {'CA'}, [Transaction Timestamp] = {'11/09/2011 11:52:02 AM'}>} [Average Cost])

Which leads me to believe the problem lies in

{"=max(timestamp#([Transaction Timestamp]))"}

Rich,

I also believe that the problem lies here, and I tried to work only on that part this with my suggested solution (hope I haven't broke anything else).

Our goal should be that the set element returns the exact above literal timestamp when evaluated.

I still think that my solution should work, I created a small demo to demonstrate (see attached). (BTW, if you clear the expression label, the expression itself will be used, but displayed with dollar sign expansion evaluated, this is quite neat for debugging purposes).

I am not sure if you need the Timestamp#() function, but it shouldn't make things worse neither. What I think you do need is the additional

{<TransCode = {'CA'} >} set expression within the max() function to get the max timestamp for only these Codes, not for all possible (see also in my example).

Hope this helps,

Stefan

[#7]

Hi Stefan,

I see that the expression works in your example but when I applied it to my app, I got zero results,

So I attached a scaled down version of the application keeping only the data that applies.

I'm certain the problem lies in a data mismatch, but I just don't see it.

Can I ask you to take a look at the script? The time stamp is created from two AS400 fields. One is from a made date and the other is a time value. The source data ties into the QVW. 

Thank you very much

Rich

[#8]

Hi Stefan,

I see that the expression works in your example but when I applied it to my app, I got zero results,

So I attached a scaled down version of the application keeping only the data that applies.

I'm certain the problem lies in a data mismatch, but I just don't see it.

Can I ask you to take a look at the script? The time stamp is created from two AS400 fields. One is from a made date and the other is a time value. The source data ties into the QVW. 

Thank you very much

Rich

[swuehl]

Yes, I think that's the problem.

You are using

timestamp#([Update Date] & ' ' & [Time Updated], 'MM/DD/YYYY hhmmss TT') As [Transaction Timestamp];

while I think you need to separate hours/minutes/seconds by :

timestamp#([Update Date] & ' ' & [Time Updated], 'MM/DD/YYYY hh:mm:ss TT') As [Transaction Timestamp];

Maybe you should also check your standard Timestamp setting, and/or be careful if you apply the date/timestamp functions without explicit format code.

Hope this helps,

Stefan

edit: attached the modified qvw, only expressions changed (not the script)

[#10]

Thank Guys,

I followed your suggestion Stefan to consistently format and using your expression and Miguel's expression, I was able to get results however, I would have hoped that the expression would have respected the uniqueness of each manufacturing order and form a set of the transaction with the max timestamp equal to code CA within the manufacturing order dimension.

It appears that the exprssions are forming a set of the max transaction timestamp without to only the order with the max timestamp. Then when selections are made the results respect the order selection and display correctly.

I updated to sample with multiple orders.

I'm sorry to lean on you, but I've tried so many iterations I'm going in a circle.

thanks,

rich