MDX,计算从月末日期开始的收入累计总和与给定债务金额匹配的天数
我有一个财务立方体,我必须将每日应收销售额计算为:
当从当月最后一天到收入总和<=当日债务金额。
例如
,2009 年 12 月 31 日,我的债务金额 = 2,500,000
31-Dec-09 30-Nov-09 15-Oct-09 31-Oct-09
收入 1,000,000 1,000,000 500,000 1,0000
累计收入总额 1,000,000 2,00,000 2,500,000 4,000,000
天数 31 30 16
2009 年 10 月 15 日累计收入为 2,500,000 相当于我的债务金额当日
天数 = 31 + 31 + 16 = 76 天。
换句话说,从所选日期向后计算收入总和,直到总和等于或超过债务人迄今为止的总余额。
任何帮助将不胜感激。
如果我解释得不够清楚或者您需要更多信息,请告诉我。
提前致谢 。
舒吉。
I have a financial cube and i have to calculate Daily Sales Outstanding as :
Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .
e.g
On 31/12/2009 my debt amount = 2,500,000
31-Dec-09 30-Nov-09 15-Oct-09 31-Oct-09
Revenue 1,000,000 1,000,000 500,000 1,0000
Cummulative sum of revenue 1,000,000 2,00,000 2,500,000 4,000,000
No of Days 31 30 16
On 15/Oct/09 cummulative revenue is 2,500,000 which equals my debt amount on that day
Count of Days = 31 + 31 + 16 = 76 Days.
In other words Sum Revenue from the selected date backwards until sum total equals or exeeds the total to date balance of the debtors.
Any help will be highly appreciated .
If i haven't explained clearly enough or if you need more information then please let me know.
Thanks in advance .
Shuchi.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您是否检查过此博客:http://consultingblogs.emc.com/christianwade/archive/2006/04/30/MDX-Sprocs-and-Scripting_3A00_-An-Interesting-Example.aspx
他介绍了几种解决此问题的方法,这对我来说听起来像是一个递归问题,因为您需要“沿着日历向后走”累加收入,直到找到累加收入达到/超过初始债务的那一天?
上面的链接应该为您提供几种不同的方法来解决这个问题,如果您遇到困难,请大声喊叫。
Have you examined this blog: http://consultingblogs.emc.com/christianwade/archive/2006/04/30/MDX-Sprocs-and-Scripting_3A00_-An-Interesting-Example.aspx
He covers a few ways of approaching this, it sounds to me like a recursive problem, in that you need to 'walk backwards up along the calendar' adding up revenue, until you find the day where the added up revenue meets/exceeds the initial debt?
The above link should give you a few different approaches to tackle this, shout if you get stuck.