Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

louisevanasdruk
New Contributor

Accumulation up to and including selected period

Hi All,

I'm searching for a formula that shows accumulation up to and including the selected period.

For example: I want the totall revenu of 2018 up to and including period 4

But I want this without selecting all 4 periods in my filterbox.

I only want to select period 4.

(Period 4 is an example, it could also be period 5 or 6 or....)

The formulas I have tried are:

1.

num(

sum({<Boekjaar={$(=max(Boekjaar))},Periode={"<=$(vCurrentPeriode)"}>} [Bedrag Saldo])

,'#.##0')

-> This one shows the totall revenu until today.

2.

rangesum(

above(

Sum({<Boekjaar={$(=max(Boekjaar))}>} [Bedrag Saldo]),0,rowno()))

-> This one shows the revenu of only period 4

I think I have to do something with my period in the formula but I have no idea what kind of formula I need.

Can anyone help me with this?

Gr Louise

Tags (1)
1 Solution

Accepted Solutions
rachel_delany
Contributor

Re: Accumulation up to and including selected period

Have you tried using the function inyeartodate? Further information could be found here.

Alternatively, how do you set the variable vCurrentPeriode?

Potentially it may work if you make vCurrentPeriode = MAX(Periode).


Hope this helps.

2 Replies
rachel_delany
Contributor

Re: Accumulation up to and including selected period

Have you tried using the function inyeartodate? Further information could be found here.

Alternatively, how do you set the variable vCurrentPeriode?

Potentially it may work if you make vCurrentPeriode = MAX(Periode).


Hope this helps.

louisevanasdruk
New Contributor

Re: Accumulation up to and including selected period

Hi Rachel,

The formula with MAX(Periode) works!

Thanks you very much!

I tried it allready in the formula but that did not work so I made a variable and I called it UpToAndIncludingPeriod.

num(

-sum({<Boekjaar={$(=max(Boekjaar))},Periode={"<=$(vUpToAndIncludingPeriod)"}>} [Bedrag Saldo])

,'#.##0')

I also tried inyeartodate but that did not work.

I probably made a mistake in the formula.

And Maybe I understood it wrong but do you have to adjust the date in that formula every year?

inyeartodate ('2018/01/01', '2018/12/31', 0)


num(

-sum({<Boekjaar={$(=max(Boekjaar))},Periode={$(vInYearToDate)}>} [Bedrag Saldo])

,'#.##0')

Gr Louise