Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I've a necessity to build a chart with an cumulative calculation.
Dimensions: Container id, Date
Measures: PRI = if day(Date) = 1 then 1 else TWRR * previous(PRI) end
TWRR is another calculation and can be disregarded.
How to implement this calculation?
Thanks.
OK, this does the trick. Thanks.
Awesome
I am glad we were able to help
And may I ask another question related to this problem?
Most probably I will end up moving this calculation to the script. Because I have a mixture of calculations on the Product level and on the Container level, it becomes literally impossible to handle them correctly inside the charts.
So, currently, I ended up doing these calculations in the script and using the following approach :
- create a sorted table of container, date
- loop on each row
- calculate the PRI (as prev_pri * TWRR)
- generate a new row with the current container, date, PRI and add it to a new table
- join this table to another table [container, product, date] for further calculations
This generation of 1 row at a time is too slow and takes around 40 min for 20k records. My real data will be around 300k records, so I can't wait for the whole day for these records to load.
Do you think there might be a faster way for doing this PRI calculation?
Thanks.
***** UPD *****
This is the calculation code inside the script:
// PRI
Performance_Container_tmp2:
NoConcatenate LOAD *
Resident Performance_Container_tmp
order by %CONT, %PERF_DATE;
let pri = 0;
let cont = '';
let prev_cont = '';
for i = 1 to NoOfRows('Performance_Container_tmp2')
// Get prev container
let prev_cont = cont;
// Get current container, date, twrr
let cont = Peek('%CONT', i, 'Performance_Container_tmp2');
let d = Peek('%PERF_DATE', i, 'Performance_Container_tmp2');
let twrr_fact = Peek('TWRR Fact', i, 'Performance_Container_tmp2');
// Set PRI to 0 if the container changed (useful when the container starts in the middle of the month)
if prev_cont <> cont then
let pri = 0;
endif
// Calc PRI
if Day(d) = 1 then
let pri = 1;
ELSE
let pri = pri * twrr_fact;
ENDIF
// Generate new row
if not IsNull(pri) then
Performance_Container_tmpPRI:
LOAD
num($(cont)) as %CONT,
date('$(d)') as %PERF_DATE,
$(pri) as PRI
AutoGenerate 1;
ENDIF
next
// Join PRI calculation to the Container temp table
Left Join (Performance_Container_tmp) LOAD
%CONT,
%PERF_DATE,
PRI as Container.PRI
Resident Performance_Container_tmpPRI;
drop Tables Performance_Container_tmp2, Performance_Container_tmpPRI;
In the script, you can try this:
Table:
LOAD....
FinalTable:
LOAD *,
If(Container_ID = Previous(Container_ID) and MonthYear = Previous(MonthYear), Peek('New_PRI') * 1.5, PRI) as New_PRI
Resident Table
Order By Container_ID, Date;
OK, this is a good starting point. Thanks.