Very complicated sum of aggregation at different level
I am trying to do a very special aggregation. I didn't find anything that correspond to my request.
I want to disagregate an annual budget like the history set if is it possible and if not, I want to disagregate the budget like the mean of comparable things. Let me explain :
I have sites (S1,S2,S3 etc.) and on each site I have several kinds of rooms (H1, H2, H3, etc.).
I have the income of the last year by occupation date and reservation date.
I have the annual budget of my sites and rooms for my futurnseason.
I want to disagregate the annual budget like the history income, when ther is an history. For example, if my budget is 100 for the site S1 and the room H1 and in my history I earn 120 in March and 80 in April, I want to display 100*120/(120+80) for this site in March and 100*80/200 in April).
What is hard to manage is that I have new sites, so I don't have history for these sites. In this case, I want to disagregate my budget like the same rooms of my history. For example, I have a budget of 100 for the room H1 in the site S4 (which is a new site). My total income for the H1 is 1000 in March and 1400 in April, so I want to display 100*1000/2400 for March and 100*1400/2400 for April.
I can display both separatly but I didn't find how to show the right sum (if I select sites S1 and S4 and room H1, I want to display 100*120/(120+80) + 100*1000/2400 for March and 100*80/200 + 100*1400/2400 for April). I tried several aggregation and sum, but nothing works properly.
I join an excel file which explain better what I want to do.
Thank you for your help, I really don't see the solution.
You better do this at script level. Even if you get to find a correct expression I think you should create the disaggregated budget records... it won't be easy to think but will make your app more efficient and easy to maintain.