I had a problem, which I already solved.
But I feel it can be solved in a more elegant way and I am sure there are much more skilled members in this forum, who may give me a hint.
I created a parameter in a planning function, which provided my Hana procedure with all needed data in a data set. No modification of the data is needed to calculate the results.
Anyway I had trouble to extract the data, so I splitted it up in a lot of tables and calcuated my values using JOINs.
The problem:
I have a table which contains the sales shares for a whole month, and also for the weeks within this month (which always sums up to 100% = the monthly share).
The formula for my weekly results is:
(weekly share 1 Product 1 * corresponding monthly share + weekly share 1 Product 2 * corresponding monthly share / (addition of all monthly shares (Product 1 and Product 2)
Would you solve this with nested SELECTs instead?
Or was my approach not so bad in the end?
Hello Oliver,
Actually, I tried to create the query for your case as follows
select distinct month, WeekNo, week_value / week_quantity as week_performance from ( select m.month, w.WeekNo, p.productid, p.percentage, m.quantity, p.percentage * m.quantity as value, sum(p.percentage * m.quantity) over (partition by w.WeekNo) as week_value, sum(m.quantity) over (partition by w.WeekNo) as week_quantity from ( select distinct week as WeekNo from WEEKLY_SALES_AS_PERCENTAGE ) as w left outer join WEEKLY_SALES_AS_PERCENTAGE p on p.week = w.WeekNo left outer join MONTHLY_SALES m on m.month = p.month and m.productid = p.productid ) as calc;
And the output is as follows
I'm not sure if this was what you have asked for but I found it useful for aggregation functions likes SUM(), Count(), and window functions like Row_Number() with Partition By clause