5 Replies Latest reply: Jan 31, 2013 7:18 AM by Christian Berg RSS

    Query to find out last quarter data

    user13315956
      Hi All,

      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.

      Thanks,
      Kamal
        • 1. Re: Query to find out last quarter data
          Srini VEERAVALLI
          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
          • 2. Re: Query to find out last quarter data
            user13315956
            Hi Valli,

            Thanks, but i am not having any quarter field in the back-end table. I'm just having the period name field in the table.

            Thanks,
            Kamal
            • 3. Re: Query to find out last quarter data
              Srini VEERAVALLI
              So what you can define it yourself as below

              case when monthname in ('jan','feb','mar') then 'Q1'
              when bla bla
              when bla bla
              when bla bla
              end
              • 4. Re: Query to find out last quarter data
                user13315956
                Hi Valli,

                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?

                Please advice,

                Thanks,
                Kamal
                • 5. Re: Query to find out last quarter data
                  Christian Berg
                  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...