Straight table - drag previous value different from zero

[oscarvictory]

Hi all,

This is my Table and what I need. I've tried below code but no success. Hope you can help me.

Many TKS.

if(Sum({< Sales= {"ABC*"} >} Qty * price_abc) > 0,

Aggr(RangeSum(Above(Sum({< Sales= {"ABC*"} >} Qty * price_abc) , 0, RowNo())), SNo),

Above(TOTAL Aggr(RangeSum(Above(Sum({< Sales = {"ABC*"} >} Qty * price_abc) , 0, RowNo())), SNo)))

[Andrei_Cusnir · Accepted solution]

Hello,

This can be easily done through script. Here are some steps:

1. Lets assume that you already have the table with DATE and SALES values. For that I am going to just use directly a load inline statement:

2. Now I add the following script:

Let vNoOfRows = NoOfRows('Dataset');

Let lastSave = 0;

FOR i=0 to $(vNoOfRows)-1
 
 //Get the date as separate value:
 Let vDate=Peek('DATE',$(i),'Dataset');
 
 //Get the current value:
 Let vValue=Peek('SALES',$(i),'Dataset');
 Trace Reading value: ${vValue};
 
 // If current value is 0 keep the old saved value
 // Othwerwise, update the value to the current read value
 Let lastSave = If(vValue=0, $(lastSave), vValue);
 Trace Last save: ${lastSave};
 
 //Save the last save value in the dataset:
 NewTable:
 LOAD 
 '$(vDate)' as DATE,
 '$(vValue)' as SALES,
 '$(lastSave)' as EXPECTED
 AUTOGENERATE 1;

NEXT i;

Drop table Dataset;

This script will iterate through all the values in the table and will generate a new table where the DATE and SALES fields remain the same, but then it adds the EXPECTED field that will have the values you need.

3. Here is the outcome:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[oscarvictory]

Hi Andrei, perfectly explained!! and it runs like a charm as they say 😀

Moreover, I was wondering if there is any function in expression when the data has already been loaded.

Thanks & Regards

[Andrei_Cusnir]

Hello,

I am glad that the solution provided helped pointing you on the right direction. Regarding your question, I was initially trying to do so via front end rather than script. I was trying to do so with function Above(), but when multiple consecutive values are missing, it wont work. The issue that I had was:
If (Value = 0, Take above, Use same value) // Pseudocode

However it will work in the use case scenario:

Value

10

0

20

0

30

....

But it won't work for use case scenario:

10

0

0

20

0

30

....

Because the first found 0 was replaced by "above", so 10 and the next 0 was replaced by "above" so again 0 in that case. Perhaps somebody can share a different solution for front end, otherwise the only solution for the specific use case scenario would be going via script for now.

I hope that this information is helpful.

[oscarvictory]

Yes, exactly what happened to me in both scenarios.

Anyway, I will adopt your pointed solution.

Many TKS!!