topic Re: Sum expression with AddMonths to a variable in App Development

Sum expression with AddMonths to a variable

Denzyl — Tue, 30 Aug 2022 08:24:58 GMT

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

vLatestDate:

Max([date])

The following code gives me the sum of [amount] where [date] equals to vLatestDate:

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

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

I have tried the following code but it does not work:

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

Re: Sum expression with AddMonths to a variable

brunobertels — Tue, 30 Aug 2022 09:25:13 GMT

may be this instead

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

Re: Sum expression with AddMonths to a variable

Denzyl — Tue, 30 Aug 2022 09:32:19 GMT

The sum of the measure is all 0.

Re: Sum expression with AddMonths to a variable

SunilChauhan — Tue, 30 Aug 2022 09:38:48 GMT

try this

create a new field

num(Date) as numDate in script;

=Sum({$<numDate={'$(=num(Addmonths(Max(numDate),-1)))'}>}Sales)

See the attached file

Re: Sum expression with AddMonths to a variable

oskartoivonen — Fri, 02 Sep 2022 13:42:52 GMT

The issue appears to be that you're not telling Qlik that to actually calculate the AddMonths-function, it's being interpreted literally instead of executing.

Try the following measure:

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

Note the addition of "="-character to the start of the dollar expansion, to tell Qlik to calculate the dollar expansion and not simply do a string replace. Sometimes the end result of your date adjustment might not meet the format of your target field, but in this case it should be fine. But if you wanted to for example do a MonthYear-field selection based on your vLatestDate, you'd have to add a formatting function after the AddMonths, such as MonthName-function or whichever format the target field is in.