11 Replies Latest reply: Nov 14, 2011 7:40 PM by Richard DeRocco RSS

    Timestamp in Set

      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:

      Cost Adjustments.gif

      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.

      Cost.gif

      Hope you can point me in the right direction.

       

      Thanks,

       

      Rich

        • Timestamp in Set
          Stefan Wühl

          Try

           

          sum({$<

          TransCode = {'CA'},

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

          >} [Average Cost])

           

          Hope this helps,

          Stefan


            • Timestamp in Set

              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

                • Timestamp in Set
                  Vlad Gutkovsky

                  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

                  • Re: Timestamp in Set
                    Miguel Angel Baeyens de Arce

                    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

                    • Re: Timestamp in Set
                      Stefan Wühl

                      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

                        • Re: Timestamp in Set

                          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

                          • Re: Timestamp in Set

                            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

                              • Re: Timestamp in Set
                                Stefan Wühl

                                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)

                                 

                                 

                                • Re: Timestamp in Set

                                  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