I trying to create an inventory returns report with a few aggregated calculated columns. I need a UI-level solution, I have tried every function I know.
I received an error when I tried to add the dataset to the post. I attached a screenshot showing the data with the solution. I will try to explain my problem clearly.
The table in the screenshot is sorted by Station and Age (desc). I need to create the following columns: Col1 - marks item with 1 if the item's age in the station is more than 90 (this part is already working). Col2 - cumulative inventory value for each station. From each station we can return up to value of 250. Col3 - Type A items are important, so each station must keep at least 10 A type units. This column should make sure that no type A items are selected for return if it would leave the station with less than 10 Type A items. ** All the conditions are defined as variables, and the user can change their values. Col4 & Col5 - Displays the inventory quantity and value only for the records that meet all 3 conditions above.
My final goal is to create another table at the station level (also shown in the screenshot) that display the current inventory, the returned inventory, and the remaining inventory.
I would really appreciate any guidance or suggestions. Thank you in advance for your help!
Could you please clarify what's really not working?
From your screenshot, I couldn't understand the 'Current Qty' column in the second table (how it sums up to 38 for station 025?), and also couldn't understand why 'Qty to Return' returns 6 for station 025, when the first table shows 7 items above 90 days of age.
Is this the problem you are facing?
Perhaps you can share here the expressions you are using for these columns, that would also help understand it better.
I used made up data. I did not create all the 38 items from the second table in the first table, but the logic is the same.
'Qty to Return' is shown only for items/records that meet these 3 conditions: * The item is more than 90 days in station. * The total return value is no more than 250. * At least 10 Type A items must stay at the station. I marked the items that meet all the condition in green in the Col4 column.
I tried using the RangeSum() and Above()/Below() functions.
If your logic depends on the granularity of station and item you will need to consider it also within a chart which contains only the station-level. This could be reached by embedding your calculation within an aggr() which may go in the direction of:
