nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to get max/min from list of dates?

PuneetSaha

I have an epic under which there are many types of tasks. I have a macro where I am able to filter only those tasks which I care. I need to find the maximum of created date of all such tasks and max of resolved date.
To attain this, I have two macros. Outer macro and inner macro.

In the inner macro, I have the list of tasks which I care. Below is the query I am using in macro.
Select Formatdate((MAX(T1.'Created")),"m/d/y") as 'latest date' from T1 groupby t1.epiclink

And in outer macro, I am referring to t2.latest date which is used to create the table.Still All I get is empty. Basically, I am trying to achieve what Pivoty table achieve for other fields. Because pivot table cannot find MIN or Max of list of dates.

Any help is much appreciated.

Accepted answers

Stiltsoft support

Hi @PuneetSaha ,

Unfortunately, you haven't mentioned the app or the macros that you are using. There are no any standard macros that work with SQL queries.

We develop the Table Filter, Charts & Spreadsheets for Confluence app though, and the app provides the Table Transformer macro that is based on the AlaSQL query. If this is the case, please contact our support portal: there you'll be able to share the source tables, the current macro structure, and the desired outcome. Then we'll guide you through the use case.

Meanwhile, if our assumptions about the app are correct, the Table Transformer macro understands the following query and returns the latest date:

SELECT MAX(T1.'Date') FROM T1

From your example, we see that your refer to your column in a strange way:

Select Formatdate((MAX(T1.'Created")),"m/d/y") as 'latest date' from T1 groupby t1.epiclink

We use ' ' to refer to columns and " " to refer to strings. So, the correct syntax is T1.'Created', otherwise you'll get an error - maybe that is what's happening for you.

Also there is a groupby typo.

If you use some other app/macro, please define it for other Community members to help.

All comments

Stiltsoft support

Hi @PuneetSaha ,

Unfortunately, you haven't mentioned the app or the macros that you are using. There are no any standard macros that work with SQL queries.

Meanwhile, if our assumptions about the app are correct, the Table Transformer macro understands the following query and returns the latest date:

SELECT MAX(T1.'Date') FROM T1

From your example, we see that your refer to your column in a strange way:

Select Formatdate((MAX(T1.'Created")),"m/d/y") as 'latest date' from T1 groupby t1.epiclink

We use ' ' to refer to columns and " " to refer to strings. So, the correct syntax is T1.'Created', otherwise you'll get an error - maybe that is what's happening for you.

Also there is a groupby typo.

If you use some other app/macro, please define it for other Community members to help.

PuneetSaha

I am using table macro, with jira issue filter macro to pull all the "task" i care from jira.

I also tried - SELECT MAX(T1.'Date') FROM T1 GROUP BY T1.'Epic Link'

The date format I selected in table macro is yy-mm-dd. All I am getting is empty in the table.

PuneetSaha

The syntax error of apostrophes or group by was typo. As I was writing here and not exactly copy paste the query. For such typo, the macro throws exception. Apology for confusion.

I guess the main problem is that macros do not throw any exception but in the table i do not see any data. The date format is a challenge, I guess. When we use jira issue filter macro and select the fields, I selected "Created" and I assume it is in format of yy-mm-dd. Is that not right?

Stiltsoft support

Firstly, we are not sure that you use our Table Transformer macro: you call yours "table macro", we don't have such macro in our app.

Secondly, we don't know what date format is set for your Jira. )

Insert the Jira Issues macro without any other macros and publish the page - then you'll see what Jira gives and will be able to adjust the format in the macro Settings if required.

If to talk about the Table Transformer macro, the provided SELECT MAX(T1.'Date') FROM T1 GROUP BY T1.'Epic Link' is a valid query and gives the required result on a simple manually created test table.

So, you may find which app are you currently using and refer to their support directly.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

date

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets

table-macro