Dynamic Aggregates on Report Rows
-
I have a requirement to calculate week to date and month to date aggregates of production measures per category. Each category includes a specification of the aggregation algorithm. I would like to tackle this using LINQ, and would like some pointers as to where to start. I'm using Linq to Entities. At worst I can loop through the categories and manually use pluggable functions to aggregate sub-queries for the running totals, but that is so clumsy it would feel like COBOL.
-
I have a requirement to calculate week to date and month to date aggregates of production measures per category. Each category includes a specification of the aggregation algorithm. I would like to tackle this using LINQ, and would like some pointers as to where to start. I'm using Linq to Entities. At worst I can loop through the categories and manually use pluggable functions to aggregate sub-queries for the running totals, but that is so clumsy it would feel like COBOL.
IT would probably be useful to provide some information on what data you're dealing with. From your explaination, I cant form any concrete ideas about a solution. Specifically:
Brady Kelly wrote:
Each category includes a specification of the aggregation algorithm.
How does it store it? What is stored? What are the expected results for a given set of data?
-
IT would probably be useful to provide some information on what data you're dealing with. From your explaination, I cant form any concrete ideas about a solution. Specifically:
Brady Kelly wrote:
Each category includes a specification of the aggregation algorithm.
How does it store it? What is stored? What are the expected results for a given set of data?
I have daily records of production per category, i.e. category A: 80 tons. Each report row will look like this: Daily Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B etc. etc. Where Category A and Category B can each have their own formula for deriving e.g. the Daily figure of 100 tons from the input figure of 80 tons, and similarly each have their own formula for determining the weekly and monthly totals, which could be some sort of average, with possible constant values and other custom requirements.
-
I have daily records of production per category, i.e. category A: 80 tons. Each report row will look like this: Daily Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B etc. etc. Where Category A and Category B can each have their own formula for deriving e.g. the Daily figure of 100 tons from the input figure of 80 tons, and similarly each have their own formula for determining the weekly and monthly totals, which could be some sort of average, with possible constant values and other custom requirements.
-
Im out. Ive stared at that for about the last 5 minutes and I have zero clue what you're trying to achive. Thats probably me being thick, rather than you not explaining your problem though!
Shit, sorry, all my spaces got eaten up, so the report example turned into something like the EDIFACT monster. :~ I'll try again. I have daily records of production per category, i.e. category A: 80 tons. Each report row will look like this: Daily Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B etc. etc. Where Category A and Category B can each have their own formula for deriving e.g. the Daily figure of 100 tons from the input figure of 80 tons, and similarly each have their own formula for determining the weekly and monthly totals, which could be some sort of average, with possible constant values and other custom requirements.
-
Shit, sorry, all my spaces got eaten up, so the report example turned into something like the EDIFACT monster. :~ I'll try again. I have daily records of production per category, i.e. category A: 80 tons. Each report row will look like this: Daily Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B etc. etc. Where Category A and Category B can each have their own formula for deriving e.g. the Daily figure of 100 tons from the input figure of 80 tons, and similarly each have their own formula for determining the weekly and monthly totals, which could be some sort of average, with possible constant values and other custom requirements.
-
Shit, sorry, all my spaces got eaten up, so the report example turned into something like the EDIFACT monster. :~ I'll try again. I have daily records of production per category, i.e. category A: 80 tons. Each report row will look like this: Daily Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B etc. etc. Where Category A and Category B can each have their own formula for deriving e.g. the Daily figure of 100 tons from the input figure of 80 tons, and similarly each have their own formula for determining the weekly and monthly totals, which could be some sort of average, with possible constant values and other custom requirements.
Would something like this get you started down the right path?
Dim byCategory = From r In records _
Group By r.Category Into GroupDim monthToDate = From td In byCategory _
Select New With {.Category = td.Category, _
.Total = Aggregate v In td.Group _
Where v.RecordDate.Month = Date.Now.Month _
Into Sum(v.Value)} -
Yeah, the problem was not with the layout - I managed to work that out - the problem is in the description you've given. I can;t get my head around what you actually want to do.
Daily (Day 2) Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B 89% 96% -7% 92% 98% -6% etc. etc.
Category A: On day 2 of the week, 10 skips of ore are mined. At 10 tons/skip, that equals the 100 tons you see above, under Daily Actual. So, for Category A, I need a formula defined that says "input * 10", but it won't always be a simple multiplication factor. I need to store any formula for the input -> Daily figure. On Day 1, I have a 132 ton Actual, but a positive error of 2 tons/day is known, so the Week to Date Actual is not just a simple sum and needs to be adjusted down. So I need a special formula for Week to Date actuals, like (d1+...+dn) - 2n. Category B: On day 2, an assay result gives x kilograms of nickel per ton of ore. For Category B, I need a daily Actual formula of nickel/ore * 100, but again, I must be able to assign any formula to Daily Actuals for Category B. For the Week to Date Actuals, do I use mean, median, or mode, and is there an adjustment? In my Categories DB table, I have e.g. Category A: UnitOfMeasure=Tons, DailyFormula="input*10",WeeklyFormula="(d1+...+dn) - 2n" My code needs to apply the unique formulae for Category A when calculating values for a Category A line in the report. I want to try and work this into a LINQ query against the raw data. -
Would something like this get you started down the right path?
Dim byCategory = From r In records _
Group By r.Category Into GroupDim monthToDate = From td In byCategory _
Select New With {.Category = td.Category, _
.Total = Aggregate v In td.Group _
Where v.RecordDate.Month = Date.Now.Month _
Into Sum(v.Value)}No, because there you are simply achieving the example I only gave to illustrate the nature of the report. See my post recent post.
-
Daily (Day 2) Week to Date Month to Date Actual Plan Var Actual Plan Var Actual Plan Var Category A 100 110 -10 230 300 -70 900 1200 -300 Category B 89% 96% -7% 92% 98% -6% etc. etc.
Category A: On day 2 of the week, 10 skips of ore are mined. At 10 tons/skip, that equals the 100 tons you see above, under Daily Actual. So, for Category A, I need a formula defined that says "input * 10", but it won't always be a simple multiplication factor. I need to store any formula for the input -> Daily figure. On Day 1, I have a 132 ton Actual, but a positive error of 2 tons/day is known, so the Week to Date Actual is not just a simple sum and needs to be adjusted down. So I need a special formula for Week to Date actuals, like (d1+...+dn) - 2n. Category B: On day 2, an assay result gives x kilograms of nickel per ton of ore. For Category B, I need a daily Actual formula of nickel/ore * 100, but again, I must be able to assign any formula to Daily Actuals for Category B. For the Week to Date Actuals, do I use mean, median, or mode, and is there an adjustment? In my Categories DB table, I have e.g. Category A: UnitOfMeasure=Tons, DailyFormula="input*10",WeeklyFormula="(d1+...+dn) - 2n" My code needs to apply the unique formulae for Category A when calculating values for a Category A line in the report. I want to try and work this into a LINQ query against the raw data.Ok, that's alot clearer. The solution(s) could also fill a book. To enable you're program to read, and understand any dynamic equations to apply to a set of data will require you write something that can "compile" (or at the very least interpret) that string. You may like to lok into the Interpreter[^] design pattern. (Also on wikipedia[^].
-
Ok, that's alot clearer. The solution(s) could also fill a book. To enable you're program to read, and understand any dynamic equations to apply to a set of data will require you write something that can "compile" (or at the very least interpret) that string. You may like to lok into the Interpreter[^] design pattern. (Also on wikipedia[^].
Thanks! That's a good pointer. I have also since found an evaluator library (in C#) that parses Excel style formulae, On which I can modify range specs to instead use collection variables.