Insert SUM when using a the maximum function

[PauloMongo]

I have a report that brings back transactions for the entire year that I summarise into various months,weeks and current day.

To get the the current day I use the below formula

//Current Day
if {TRANSACTIONS.TRAN_DATE} = maximum({TRANSACTIONS.TRAN_DATE}) then
{TRANSACTIONS.TRAN_AMT}

This returns the result as expected, however I need to summarise this value into the two groups I have created, the option to summarise is not available, any help most welcome

 

[lbass]

If you really mean "current day" then you could instead use:

if {TRANSACTIONS.TRAN_DATE} = currentdate then
{TRANSACTIONS.TRAN_AMT}

If you mean the most recent date, then the solution depends in part upon your CR version. Also please specify how you are trying to group using this formula.

-LB

 

[PauloMongo]

sorry, the current day is not the system date, it was the previous working day on the system which could be a Friday and the report could run on a sunday night.

I have other fields that I group on that are stored at transaction level. I am currently using CRXI Release 1.

Look forward to your reply.

Paul

 

[lbass]

You could use a formula like:

if dayofweek(currentdate) = 1 then
if {TRANSACTIONS.TRAN_DATE} = currentdate-2 then
{TRANSACTIONS.TRAN_AMT} else
if dayofweek(currentdate) = 2 then
if {TRANSACTIONS.TRAN_DATE} = currentdate-3 then
{TRANSACTIONS.TRAN_AMT} else
if dayofwfeek(currentdate) in 3 to 7 then
if {TRANSACTIONS.TRAN_DATE} = currentdate-1 then
{TRANSACTIONS.TRAN_AMT}

If this still doesn't quite work for you because of variation in working days, then you could use "add command" to get the most recent date:

select max(table.`date`)as maxdate, table.`ID`
from `table` table
group by table.`ID`

If you are looking for max dates within groups (here I assumed an ID field), you would add in your groups and then link the command to your main table fields on the date and group fields. {command.maxdate} would be available for grouping formulas.

-LB