topic Re: Sum expression where a date column matches a date variable in App Development

Sum expression where a date column matches a date variable

Denzyl — Mon, 29 Aug 2022 13:02:49 GMT

I created a variable vLatestDate which is derived from the max month of the [date] column

vLatestDate:

=Max([date])

I am now trying to create a column that is a sum expression based on the [date] column matching the vLatestDate variable.

I have tried the follow, but it did not work:

SUM({<[date]=vLatestDate>} [amount])

However, the following code does:

SUM(IF([date]=vLatestDate, [amount])

Why does the first code not work while the second does?

Or — Mon, 29 Aug 2022 13:45:30 GMT

You seem to have left out the $() required to parse the variable.

Try instead:

SUM({<[date]=$(vLatestDate)>} [amount])

SUM({<[date]= {'$(vLatestDate)'} >} [amount])

Denzyl — Tue, 30 Aug 2022 02:11:15 GMT

The following code worked:

SUM({<[date]= {'$(vLatestDate)'}>} [amount])

Denzyl — Tue, 30 Aug 2022 07:26:08 GMT

Would it be possible to get the [amount] where [date] = vLatestDate - 1 month?

I have tried the following codes but it doesn't work:

SUM({<[date]= {'$(addmonths(vLatestDate, -1))'}>} [amount])

and

SUM({<[date]= {$(addmonths(vLatestDate, -1))}>} [amount])

the expression shows blank values:

Or — Tue, 30 Aug 2022 09:33:13 GMT

Have a look at e.g. this thread for examples of using an expression in the set analysis comparison.