0 Replies Latest reply: Nov 19, 2012 6:08 AM by 963994 RSS

    getting last year sale , this year sale and getting there percentage

    963994
      Hi,
      I am using three query in my report
      first query : to get this year actual sales
      second query : to get last year actual sales
      third query : to get target sales

      my first query is
      select t.branch_cd,b.branch_e_name,t.vndr#,v.vndr_name,
      sum(nvl(t.sales_actl_amt,0)) sales_actl_amt
      from inv_sales_trgt_val t,branches b,vendor v where
      t.branch_cd=b.branch_cd and
      t.vndr#=v.vndr# and
      (t.yymm between :fiscal_month and :fiscal_month2) and
      (:fiscal_month<>trunc(:fiscal_month2,-2)) and :fiscal_month2<>trunc(:fiscal_month2,-2)) and t.branch_cd between :from_branch and to_branch and
      t.vndr# between :from_vndr and :to_vndr
      group by t.vndr#,v.vndr_name,t.branch_cd,b.branch_e_name
      order by t.vndr#,t.branch_cd;

      my second query
      select t.branch_cd,b.branch_e_name,t.vndr#,v.vndr_name,
      sum(nvl(t.sales_actl_amt,0)) sales_actl_amt0 ,
      sum(nvl(t.sales_actl_amt,0)) last_year_sales_actl_amt
      from inv_sales_trgt_val t,branches b,vendor v where
      t.branch_cd=b.branch_cd and
      t.vndr#=v.vndr# and
      (t.yymm between :fiscal_month-100 and :fiscal_month2-100) and
      (:fiscal_month<>trunc(:fiscal_month2,-2)) and :fiscal_month2<>trunc(:fiscal_month2,-2)) and t.branch_cd between :from_branch and to_branch and
      t.vndr# between :from_vndr and :to_vndr
      group by t.vndr#,v.vndr_name,t.branch_cd,b.branch_e_name
      order by t.vndr#,t.branch_cd;

      and my third query is
      select t.branch_cd,b.branch_e_name,t.vndr#,v.vndr_name,
      sum(nvl(t.sales_actl_amt,0)) sales_actl_amt ,
      sum(nvl(t.sales_trgt_amt,0)) sales_trgt_amt
      from inv_sales_trgt_val t,branches b,vendor v where
      t.branch_cd=b.branch_cd and
      t.vndr#=v.vndr# and
      (t.yymm between :fiscal_month and :fiscal_month2) and
      (:fiscal_month<>trunc(:fiscal_month2,-2)) and :fiscal_month2<>trunc(:fiscal_month2,-2)) and t.branch_cd between :from_branch and to_branch and
      t.vndr# between :from_vndr and :to_vndr
      group by t.vndr#,v.vndr_name,t.branch_cd,b.branch_e_name
      order by t.vndr#,t.branch_cd;

      in second query i am created two summary column and one place holder column and one formula column to get the percentage % :

      cs_3=sales_actl_amt
      cs_4=last_year_sales_actl_amt
      cp_2
      cf_1(example :cp_2=cs_3/cs_4)
      I am getting all 100 percentage.

      in the third query i created two summary column and one place holder column and one formula column to get the percentage % :
      cs_1=sales_actl_amt
      cs_2=last_year_sales_trgt_amt
      cp_1
      cf_1(example :cp_1=cs_1/cs_2)
      Here i am getting correct percentage

      I need to get cf_1 percentage but it is getting 100 for all

      its urgent please help.
      thank you.