topic Re: sum function with SetExpression in QlikView

sum function with SetExpression

Anonymous — Tue, 25 Oct 2016 13:28:14 GMT

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

sunny_talwar — Tue, 25 Oct 2016 16:11:28 GMT

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

Anonymous — Mon, 31 Oct 2016 09:54:09 GMT

thank Sunny T, your expression is correct

Re: sum function with SetExpression

Anonymous — Thu, 03 Nov 2016 10:24:02 GMT

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

sunny_talwar — Thu, 03 Nov 2016 16:34:59 GMT

What is the output needed here?

Re: sum function with SetExpression

Anonymous — Thu, 03 Nov 2016 16:42:45 GMT

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

Re: sum function with SetExpression

sunny_talwar — Thu, 03 Nov 2016 17:23:46 GMT

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

Re: sum function with SetExpression

Anonymous — Fri, 04 Nov 2016 09:26:42 GMT

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

sunny_talwar — Sun, 06 Nov 2016 16:31:32 GMT

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