topic Re: How to pivot a table on datetimes as values? in App Development

How to pivot a table on datetimes as values?

alespooletto — Mon, 11 Dec 2023 13:29:44 GMT

I was able to create this pivot table with the desired values as columns, but the problem is with the values. I want to show DateTimes as values under these columns, but how can i do so?

Re: How to pivot a table on datetimes as values?

vincent_ardiet_ — Mon, 11 Dec 2023 15:16:01 GMT

What is your expression? We can only see a portion of it.

Re: How to pivot a table on datetimes as values?

alespooletto — Mon, 11 Dec 2023 15:32:16 GMT

It's

Max([Date.autoCalendar.YearsAgo])

Re: How to pivot a table on datetimes as values?

vincent_ardiet_ — Mon, 11 Dec 2023 15:39:01 GMT

YearsAgo returns the number of past years between your stored dates and today. So, this is excepted not to see a date.
You need to pick another field like autocalendar Date or basically the original date field of your datamodel (specially if you want also the time).
Max([Date.autoCalendar.Date])

Re: How to pivot a table on datetimes as values?

alespooletto — Mon, 11 Dec 2023 15:46:30 GMT

Thank you, this indeed does show dates instead of numbers. The thing is, I was interested in knowing the DateTime, but I cannot seem to find an autoCalendar.DateTime, is there a workaround for this?

Re: How to pivot a table on datetimes as values?

vincent_ardiet_ — Mon, 11 Dec 2023 15:50:33 GMT

The default script of autoCalendar is doing a Floor on the original date, so this is removing the time.
If you want to see it, use the original field linked to it [Date]:
Max([Date])

Re: How to pivot a table on datetimes as values?

alespooletto — Tue, 12 Dec 2023 08:04:07 GMT

Thank you @vincent_ardiet_ ,it seems this does show dates still, not datetimes.

EDIT: I was using the wrong field, this shows the datetime as you mentioned it would.

Re: How to pivot a table on datetimes as values?

vijay121 — Tue, 12 Dec 2023 12:49:46 GMT

Pivoting a table on date times as values typically involves transforming a dataset where date-time values are stored in rows into a structure where those date times become column headers.

Keep in mind that if you have duplicate entries for the same date-time value, the pivot function won't work unless you first handle these duplicates. You may need to aggregate or transform the data accordingly before pivoting.

Re: How to pivot a table on datetimes as values?

alespooletto — Thu, 14 Dec 2023 08:55:34 GMT

Is there a way to sort the pivot table by the datetimes? As I want to sort by the earliest to the latest datetime @vincent_ardiet_

Re: How to pivot a table on datetimes as values?

vincent_ardiet_ — Thu, 14 Dec 2023 09:01:23 GMT

Yes if you go in the "Sorting" section, you will see "Sort by first measure" (according to the fact that your max(Date) expression is still the first one).

Re: How to pivot a table on datetimes as values?

alespooletto — Thu, 14 Dec 2023 09:53:36 GMT

Thank you @vincent_ardiet_ , I tried to do that but it seems they aren't ordered:

Should I sort by another dimension?

Re: How to pivot a table on datetimes as values?

vincent_ardiet_ — Thu, 14 Dec 2023 13:21:19 GMT

Sorting pivot table is always painful. And you have also NULL cells (according to your first screenshot) and this is not helping.

Re: How to pivot a table on datetimes as values?

alespooletto — Thu, 14 Dec 2023 13:25:49 GMT

Yes, indeed there are some NULLs which I am dealing with in the Load Editor.