Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
rachel_delany
Creator II
Creator II

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.

View solution in original post

2 Replies
rachel_delany
Creator II
Creator II

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.

Anonymous
Not applicable
Author

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