I need a SQL query to find out. I have an amount field like below
Period | Amt | Amt2
Jul | 0 | 0+20+30
Aug | 20 | 0
Sep | 30 | 0
Oct | 0 | 0+50
Nov | 50 |
My need is Amt2 which needs to calculating in Qtr wise. Can any one post me a query for this.
Since you posted in BI, I would like to give suggestion in BI to get what you need, just try it. This would helps you clear in BI basics :)
Create a level based metric for sum(amt) named as amt2 and set content tab to Quarter level
create a nother metric sum(amt) named as amt1 and set to the content tab to detail level
in report pull Period,amt, amt2 and run it and get the physical query and finally mark this as correct
Edited by: Srini VEERAVALLI on Jan 29, 2013 10:01 AM
Thanks, so we need to create a function and then call that from report. I have doubt in this, for example if the user enter 'SEP-12' then it will find which QTR from the case statement. But if the user have account number which is available in all the months from 2010 - 2012.
So now user will give two inputs like period_name='SEP-12' and Account Number=101010 then it should give output like sum(Q3) in the year of 2012. Here how it will calculate year wise? or do we need to specify any condition here?
How about having a proper time dimension with a quarter level which automatically aggregates you the data on each level needed and allows you to create level-based measures and do proper time-series calculations in the RPD already?
Might be a good idea and definitely more re-usable and robust than pure calcs and CASE WHEN's...