topic Re: Accumulation freezing on today's date for prior year in Data Movement & Streaming

Accumulation freezing on today's date for prior year

bskemp01 — Wed, 19 Mar 2025 22:11:20 GMT

For some reason when looking at an accumulation for current YTD and all of prior year (not prior YTD) the accumulation for last year stops at today for prior EOY. see table below:

here is the expressions in my table:

Date - Dimension:

Date(MakeDate(Year(Today())-1,Month,DayNumber), 'MMM-D')

REV YTD:

{$< Year={'$(=max([Year]))'}, [DateType]={'Invoiced'} >} // 0 = USE, 1 = CAD if(Currency_Code=0, Sum([Company 1 Sales]) + Sum((1/[Rate])*[Company 2 Sales CAD]) + Sum([Company 3 Sales]) + Sum((1/[Rate])*[Company 4 Sales CAD]) , Sum(Rate*[Company 1 Sales]) + Sum([Company 2 Sales CAD]) + Sum(Rate*[Company 3 Sales]) + Sum([Company 4 Sales CAD]) )

EOY PY:

{< Year={'$(=max([Year])-1)'}, [DateType]={'Invoiced'} >} if(Currency_Code=0, Sum([Company 1 Sales]) + Sum((1/[Rate])*[Company 2 Sales CAD]) + Sum([Company 3 Sales]) + Sum((1/[Rate])*[Company 4 Sales CAD]) , Sum(Rate*[Company 1 Sales]) + Sum([Company 2 Sales CAD]) + Sum(Rate*[Company 3 Sales]) + Sum([Company 4 Sales CAD]) )

also i do have the Year field as a required field:

Re: Accumulation freezing on today's date for prior year

theoat — Fri, 23 Feb 2024 15:11:14 GMT

How is calculated your accumulation ?

Re: Accumulation freezing on today's date for prior year

bskemp01 — Fri, 23 Feb 2024 15:14:29 GMT

im using the EOY PY twice in the table and am setting the second measure to have full accumulation

Re: Accumulation freezing on today's date for prior year

theoat — Fri, 23 Feb 2024 15:24:11 GMT

Can you show me the settings of this accumulator ?

Re: Accumulation freezing on today's date for prior year

bskemp01 — Fri, 23 Feb 2024 15:26:35 GMT

sure

Re: Accumulation freezing on today's date for prior year

bskemp01 — Fri, 23 Feb 2024 16:15:46 GMT

here is an example of the problem i am facing. i have made and exported this app for your convenience.

make sure to set the Year field to required. if doing so causes the accumulations to go crazy just delete them, duplication the EOY and set accumulation to full again.

UPDATE: I HAD TO EDIT THIS AND REUPLOAD THE FILE, I FORGOT TO MAKE SHEETS PUBLIC WHEN EXPORTING

Re: Accumulation freezing on today's date for prior year

bskemp01 — Fri, 23 Feb 2024 17:12:40 GMT

i seemed to have figured out my own problem again 😊

what i did was removed the accumulation from the measure and used a RangeSum in the expression itself:

{$< Year={'$(=max([Year]))'}, [DateType]={'Invoiced'} >} if(Currency_Code=0, RangeSum(Above(TOTAL Sum(Company 1 Sales),0,RowNo())) + RangeSum(Above(TOTAL Sum((1/[Rate])*[Company 2 Sales CAD]),0,RowNo())) + RangeSum(Above(TOTAL Sum([Company 3 Sales]),0,RowNo())) + RangeSum(Above(TOTAL Sum((1/[Rate])*[Company 4 Sales CAD]),0,RowNo())) , RangeSum(Above(TOTAL Sum(Rate*[Company 1 Sales]),0,RowNo())) + RangeSum(Above(TOTAL Sum([Company 2 Sales CAD]),0,RowNo())) + RangeSum(Above(TOTAL Sum(Rate*[Company 3 Sales ]),0,RowNo())) + RangeSum(Above(TOTAL Sum([Company 4 Sales CAD]),0,RowNo())) )

i believe the measure itself was trying to limit the accumulation to todays date or something like that but using the RangeSum it only limits itself to the data. thats my theory at least. but its working now. thank you for helping me to work through the problem 😊

Re: Accumulation freezing on today's date for prior year

theoat — Fri, 23 Feb 2024 17:14:49 GMT

Good job !

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.