(again) on dates and timestamps

[#1]

Hi,

I have a Unix timestamp in my table. I convert this value to date & time in my load script to a format I want it to be.

I also managed to get these together using =dual(formatted date&time, timestamp) so that in my pivot table the date is displayed as calculated dimension.

Now I want to display only the min value (thus not all of them) of timestamp but show it as formatted date. I tried, I believe, all the permutations I could have thought of in the calculated dimension editor, adding text and num respectively to the dual expression, around it, around the min value etc. However, as soon as I use the min (or max for that matter), I get //error in calculation.

Any ideas how to get this to work?

The structure of my data:

In my data, there is a batch. In each batch, number of items exists. One of my expressions is the total number of items in a certain batch. Each item also has a timestamp. This is the value I want to know the minimum for, formatted as date.

Thanks in advance!

Jan

[Gysbert_Wassenaar]

You probably need to use the aggr function. Something like aggr(min(timestamp),BatchNumber). If that works add the formatting.

[#3]

Hi Gysbert,

Thank you, that gets me indeed one step further. I now see a timestamp next to each batch, exactly as I wanted.

Is this the way to go on?

=aggr(dual(dateFromDate,min(dateFromTimestamp)), fullBatchConverted)

I do not get to see the formatted date this way for all the rows. However, that could be a data problem on my side, not an issue with the expression...

[#4]

Ok - I found another way:

I just ditch my conversion from MYSQL and perform timestamp conversion as suggested elsewhere on the QV forum

=ConvertToLocalTime(Date(25569 + (aggr(min(timestamp), batch) / 86400)))