In FDI how to execute detail calculations before aggregation of data

Paul Therrien

In FDI how to execute detail level calculations before aggregation of data. Using a custom subject area I am trying to execute detail level calculations on data before the data can be summed up.

We have detailed shipment records - one line per item shipped - with a status of either 'shipped' or 'returned' and positive values for item price, shipping charges, and tax. Depending on the status and invoice date or return date we want these values to be either treated as positive ('shipped') or negative ('returned') values. 

Currently, FDI is accumulating the values before executing the the calculations and is summing up all numbers first, then determining whether the values are negative or positive.  Which is not correct.

We need the values to be net - sales less returns. The detail values need to be set to positive or negative before they are aggregated.

Is there a way in FDI to do the above?  Or would we better off doing the detailed calculations in a dataset and then using the dataset to produce the FDI workbook?

Any help appreciated.

Paul

RVohra

Hi @Paul Therrien,

As per my understanding, FDI semantic layer is designed for aggregation or dimensional analysis, not for row-by-row accounting logic. Looking at above requirement, I would do row-level sign logic in the Dataset SQL, then FDI only aggregates the already-correct numbers.

Thanks

Paul Therrien

Thanks for replying to my post. That is my understanding as well. I was hoping my understanding was limited and that there was some way to do this within a workbook.

RVohra

@Paul Therrien , I agree there might be some hack, but it is going to be very complex or something which might not work seamlessly.

RVohra

As per community guidelines, appreciate if we can mark the thread as "Accepted Answer" so that we can make it easier for community members to find replies that might help them in a similar situation. Replies that are accepted in this way are copied to the top of the thread, allowing other community viewers of that same question to find the "best" answer quickly. 

Paul Therrien

Thanks for that gentle nudge… I have accepted your first response as an answer.