asked on July 1, 2021
Hello,
Please see the attached screenshot.
Objective: To be able to show the Current Stock Level of Items and Batches by location.
The first screenshot in the doc attached shows the flat data to help explain the following: I have rows of individual stock transactions for "Milk Product" items. There can be many batch numbers under any given product. We pull in the "Current Stock Level" (CSL) from another database and put that value against every single stock transaction line matching on item and batch, which means we have duplicate CSL's in the flat data. We can't just Sum the CSL as it would give a total much bigger than the real CSL. However, if we Average the CSL it removes the duplicates as all the duplicate rows have the same CSL, so dividing the total by the number of rows give us the correct CSL value against the item and batch. See the second screenshot in the attached.
However, if I want to collapse and expand the item, "Donated Breast Milk", the value shows the aggregated average of all the transactions (regardless of batch), which is correct but doens't meet my need. See the third screenshot. What I'd like is for it to sum the averages of the batches and show this, like it does if I highlight and drag the cells (see the second screenshot) so that my user can see a total CSL of item but also expand to see a total CSL per batch.
Is there any way I can do this within the UI or using a calculated field / formula?
Alternatively, is there another way I can show the CSL without aggregating it when at the batch level but for it to sum when at the item level? Or a way to remove duplicate item / batch combinations? All within the UI?
If these things aren't possible within the UI, then are there alternatives using the API?
Many Thanks
⋅ July 2, 2021
Hello, Chris,
Thank you for reaching out to us.
If we understand correctly, you would like to apply the sum aggregation over values previously aggregated using average.
Please note that Flexmonster only allows applying a single aggregation rule for both cases.
However, in your case, the average aggregation serves as a workaround for the "CSL" measure that should not be summarized. Instead, we would suggest filtering the dataset before passing it to Flexmonster. For example, you could filter out all duplicates that represent the same tuples ("MILK TYPE", "BATCH NUMBER", "LOCATIONNAME", "SCL"). In this case, you could apply the sum aggregation to receive the expected result.
Here is a sample demonstrating this approach: https://jsfiddle.net/flexmonster/km3daptg/.
The getData function is responsible for filtering the initial dataSet and passing only unique members to Flexmonster.
Do you think this approach can be used in your case?
Looking forward to your feedback.
Regards,
Illia
⋅ July 2, 2021
Thanks Illia, yes this would work if we modify the code. I was hoping there would be a UI solution to this but based on your response, I assume there is not. I will put this to our development team for consideration. Thanks
⋅ July 5, 2021
Hello,
Thank you for your feedback.
We want to confirm that we did not found any solutions for this matter on the Flexmonster side. This is due to the specific workaround with averages used in your case.
We hope filtering the dataset will work for you.
Feel free to contact us if other questions arise.
Best regards,
Illia
