topic Timestamp in Set in QlikView

Timestamp in Set

Mon, 14 Nov 2011 16:24:27 GMT

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

Timestamp in Set

swuehl — Mon, 14 Nov 2011 16:38:14 GMT

Try

sum({$<

TransCode = {'CA'},

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

>} [Average Cost])

Hope this helps,

Stefan

Timestamp in Set

Mon, 14 Nov 2011 18:10:24 GMT

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

vgutkovsky — Mon, 14 Nov 2011 18:46:17 GMT

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 — Mon, 14 Nov 2011 18:49:15 GMT

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

swuehl — Mon, 14 Nov 2011 20:44:35 GMT

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

Mon, 14 Nov 2011 21:37:06 GMT

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

Mon, 14 Nov 2011 21:37:59 GMT

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

swuehl — Mon, 14 Nov 2011 21:53:14 GMT

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

Tue, 15 Nov 2011 00:04:03 GMT

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

Timestamp in Set

swuehl — Tue, 15 Nov 2011 00:19:45 GMT

Try

=FirstSortedValue({<TransCode = {'CA'}>} [Average Cost],-[Transaction Timestamp])

as expression.

Regards,

Stefan

Timestamp in Set

Tue, 15 Nov 2011 00:40:15 GMT

Thank's for sticking in there with me Stefan.

Rich