Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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

1 Solution

Accepted Solutions
Highlighted

For the sample you have provided

DATEOPENING_BALANCE
01/01/201658.774
01/02/201654.042
01/03/201652.703
01/04/201648.435
01/05/201656.686
01/06/201656.648
01/07/201646.892
01/08/201642.110
01/09/201638.551
01/10/201617.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

Capture.PNG

Mar-2016 selected

Capture.PNG

Apr-2016, May-2016, Jun-2016

Capture.PNG

View solution in original post

8 Replies
Highlighted

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)

Highlighted
Contributor
Contributor

thank Sunny T, your expression is correct

Highlighted
Contributor
Contributor

I have a new question on this topic.

Consider the following table:

  

DATEOPENING_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

Highlighted

What is the output needed here?

Highlighted
Contributor
Contributor

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

Highlighted

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

Highlighted
Contributor
Contributor

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)

Highlighted

For the sample you have provided

DATEOPENING_BALANCE
01/01/201658.774
01/02/201654.042
01/03/201652.703
01/04/201648.435
01/05/201656.686
01/06/201656.648
01/07/201646.892
01/08/201642.110
01/09/201638.551
01/10/201617.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

Capture.PNG

Mar-2016 selected

Capture.PNG

Apr-2016, May-2016, Jun-2016

Capture.PNG

View solution in original post