8 Replies Latest reply: Nov 6, 2016 11:31 AM by Sunny Talwar

# sum function with SetExpression

Hi,

who can help me to understand the error?

it's a sum of the variable MSCONTOTAB.SALDO_INIZIALE for the first selected month

sum({<

MSCONTOTAB.MESEANNO_DEMISSIONE = MONTHNAME(min(

{<MSCONTOTAB.ANNO_DEMISSIONE ={"\$(=\$(vAnno))"}>}

MSCONTOTAB.DEMISSIONE))

>}MSCONTOTAB.SALDO_INIZIALE)

SUM function does not recognize the variable MSCONTOTAB.SALDO_INIZIALE

thanks, eg

• ###### Re: sum function with SetExpression

Not entirely sure what you are trying to do, but may be try this:

Sum({<MSCONTOTAB.MESEANNO_DEMISSIONE = {"=MSCONTOTAB.MESEANNO_DEMISSIONE = MonthName(Min({<MSCONTOTAB.ANNO_DEMISSIONE ={'\$(=\$(vAnno))'}>} MSCONTOTAB.DEMISSIONE))"}>} MSCONTOTAB.SALDO_INIZIALE)

• ###### Re: sum function with SetExpression

thank Sunny T, your expression is correct

• ###### Re: sum function with SetExpression

I have a new question on this topic.

Consider the following table:

 DATE OPENING_BALANCE 01/01/2016 58.774 01/02/2016 54.042 01/03/2016 52.703 01/04/2016 48.435 01/05/2016 56.686 01/06/2016 56.648 01/07/2016 46.892 01/08/2016 42.110 01/09/2016 38.551 01/10/2016 17.749

if I select more months, the result of the function will be the OPENING BALANCE of the smallest month.

Sunny's function makes the sum, so it is not correct for this new case.

thank, enrico

• ###### Re: sum function with SetExpression

What is the output needed here?

• ###### Re: sum function with SetExpression

if I select all the dates, the output is OPENING BALANCE = 58,774 because it has minimum selected date

• ###### Re: sum function with SetExpression

This is not what you want right? What do you want to see with and and without selections?

• ###### Re: sum function with SetExpression

If I select a month, output is the OPENING BALANCE value of the selected month.

For example, I select mar-2016 I get 52.703

If I select more months, output is the OPENING BALANCE value of the smallest selected month.

For example, I select apr-2016 may-2016 jun-2016 I get 48.435

If I do not select, output is the month OPENING BALANCE value of January, then 58.774

In SetExpression that you've written the filter does not work in the sum, but if you use the filter only it works

Sum({<MSCONTOTAB.MESEANNO_DEMISSIONE = {"=MSCONTOTAB.MESEANNO_DEMISSIONE =MonthName(Min({<MSCONTOTAB.ANNO_DEMISSIONE ={'\$(=\$(vAnno))'}>} MSCONTOTAB.DEMISSIONE))"}>} MSCONTOTAB.SALDO_INIZIALE)

• ###### Re: sum function with SetExpression

For the sample you have provided

 DATE OPENING_BALANCE 01/01/2016 58.774 01/02/2016 54.042 01/03/2016 52.703 01/04/2016 48.435 01/05/2016 56.686 01/06/2016 56.648 01/07/2016 46.892 01/08/2016 42.110 01/09/2016 38.551 01/10/2016 17.749

and the expected output you are looking for, this expression should work.

Sum({<DATE = {"\$(=Date(Min(DATE), 'DD/MM/YYYY'))"}>} OPENING_BALANCE)

No selection

Mar-2016 selected

Apr-2016, May-2016, Jun-2016