Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 😊
How is calculated your accumulation ?
im using the EOY PY twice in the table and am setting the second measure to have full accumulation
Can you show me the settings of this accumulator ?
sure
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
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 😊
Good job !
Enjoy your Qlik.
Kind regards,
Théo ATRAGIE.