over intersect((some conditions),previous(%%)) works unexpectedly. Please help !

[Jihye Kim]

hello!

Trying to use over function with previous, I found it didn't work as I expected.

This is what I did and it does't work for the first intersect group.

please let me know which part i did wrong .

Thank you!

 

calculated column [test2]

= Avg( [VALUE]) over (intersect([iTEM_ID],Previous([time])))

and [test2] works like below

 

expected result would contain CD1,2020-05-20,1,3

[David Boot-Olazabal]

Hi beingolaf,

The problem comes from the fact that your timestamps, are not a continuous scale. If you change the time for the second row from May 30th to May 25th, you will see that your calculated column is exactly working like you expected.

But in the above case, the node gets broken and will not display the value of 3, as you expected.

I don't know yet how you could sort this one out. I will try to come up with something, if any.

kind regards,

David

[David Boot-Olazabal]

Hi beingolaf,

Please find attached a .dxp in which I have added a rowid (calculated) column. And instead of previous([time]) in the calculated expression you have created, I used this rowid column. And this gives you the correct results.

Have a look and see if this is working for you as well.

Kind regards,

David