11 Replies Latest reply: Feb 4, 2013 7:05 PM by KBabu RSS

    Summation Issue - Table View

    KBabu
      Hi

      I have 3 columns (Area Name, Area Desc, Area Num, Amount). I am using 10g obiee

      User requested me with some condition to populate the name based on Number like below

      Ex: CASE WHEN "Area" = 68 AND "Area Desc" = 'Layout' THEN 'Colony' ELSE NULL END

      No Probs in results for showing Area as 'Colony'...but problem is when viewing in TABLE view clicking Summation (Sigma symbol)...Results are not correct..!!!

      Area Area Desc Amount
      70 abc 1
      69 def 2
      Colony Layout 3
      71 gfg 4

      Summation when i totals after............i am getting '1'...but actually sum is '10'

      but when i remove any one of the above condition (Say "Area Desc" = 'Layout') and viewing the table and doing Summation... Results are coming correctly (ie 10)...Why it is like that...

      Can i get a help

      Edited by: KBabu on Jan 24, 2013 5:11 PM
        • 1. Re: Summation Issue - Table View
          Srini VEERAVALLI
          can you check how it works when you add another column with expression as rsum(Amount).
          Also just in case set '' instead of null in the expression.
          • 2. Re: Summation Issue - Table View
            KBabu
            Hi Srini

            Thanks for reply.
            When i add rsum(amount)...its just calculating row sum...and while totalling....getting not correct total...

            Area Area Desc Amount rsum(Amount)
            70 abc 1 1
            69 def 2 3
            Colony Layout 3 6
            71 pqr 4 10

            Summation in Table view on Area (Case Stmt is on 'Area' field)
            -------------------------------------------------------------------------------------------------------------
            1 3
            -------------------------------------------------------------------------------------------------------------

            Edited by: KBabu on Jan 24, 2013 7:23 PM
            • 3. Re: Summation Issue - Table View
              Srini VEERAVALLI
              Its by bad: Your case statement suppose to be like this

              CASE WHEN "Area" = 68 AND "Area Desc" = 'Layout' THEN 'Colony' ELSE Area END

              You should not use NULL at all, and not sure how come you get other values they suppose to be NULL as per your case statement.

              Edited by: Srini VEERAVALLI on Jan 24, 2013 9:25 PM
              • 4. Re: Summation Issue - Table View
                KBabu
                Yes I wrote the same previously as what you told...
                Actually its quite big CASE stmt....so i just took an example.............. Original is as below

                CASE WHEN "Account Number" <>(NotEqualTo Symbol) 6840 AND "Description" = 'C&P' (not equal to symbol is not coming here )
                THEN 'Spend'
                WHEN "Account Number" = 6840 AND "Description" = 'C&P'
                THEN 'Recovery'
                ELSE ''Account Number" END

                but could you please help me out why i am getting different totals. I dint understand why its happening.


                Account Number     Status Description     Amount
                6840 recovery fdfdsf 10
                6841 Spend fdsfsfd 20
                6842 Spend fdfsdf 30



                Status field where i wrote the above case statment..... and made Summation on Table view on Status
                ...so generally... for 'Recovery' it will be 10 & 'Spend' it will be 50

                But I am getting only 10 (Clicking on Sigma) Totalling


                and if you made summation on Account number...yes, i am getting 60..which is correct..
                and if you made summation on Description...yes, i am getting 60..which is correct..
                and if you made summation on Status..........no...i am getting 10...which is not correct

                and after trying when i remove the condition "Description" = 'C&P' ...and if you made summation on Status..........YES...i am getting 60..which is correct...but User want that condition...

                Can you help me please

                Edited by: KBabu on Jan 24, 2013 8:38 PM

                Edited by: KBabu on Jan 24, 2013 8:39 PM

                Edited by: KBabu on Jan 24, 2013 8:40 PM

                Edited by: KBabu on Jan 24, 2013 8:40 PM
                • 5. Re: Summation Issue - Table View
                  Srini VEERAVALLI
                  This part I'm not clear

                  CASE WHEN "Account Number" (NotEqualTo Symbol) 6840 AND "Description" = 'C&P' (not equal to symbol is not coming here )
                  THEN 'Spend'
                  WHEN "Account Number" = 6840 AND "Description" = 'C&P'
                  THEN 'Recovery'
                  ELSE ''Account Number" END

                  Since ''Account Number" is number type and THEN part in CASE statement is String type, I've no clue how it got validated?
                  • 6. Re: Summation Issue - Table View
                    KBabu
                    I mean to say the symbol of CASE WHEN "Account Number" <> 6840 AND "Description" = 'C&P'

                    'Not Equal to' Symbol (Lessthan Greaterthan) is not coming when i am typing in our forum page

                    Account Number is 'Varchar' type

                    Edited by: KBabu on Jan 24, 2013 9:18 PM

                    Edited by: KBabu on Jan 24, 2013 9:19 PM

                    Edited by: KBabu on Jan 24, 2013 9:20 PM
                    • 7. Re: Summation Issue - Table View
                      Srini VEERAVALLI
                      You can send me email right?

                      are you using as below?
                      CASE WHEN "Account Number" >='6840' AND "Description" = 'C&P'
                      • 8. Re: Summation Issue - Table View
                        KBabu
                        I sent mail to your ID explaining the issue...
                        • 9. Re: Summation Issue - Table View
                          KBabu
                          Hi Sreeni

                          Did you got my Mail....Could you please help me ?
                          • 10. Re: Summation Issue - Table View
                            KBabu
                            Sreeni... Did you got Chance to look at my condition on how to re-write the condition please.........!!!!!!!!!!
                            • 11. Re: Summation Issue - Table View
                              KBabu
                              Hey All...

                              Finally Cracked......!!!!

                              This issue is resolved in such a way........... i created a new presentation column by writing the formula (Concatenating the two columns )

                              So, when i write a case stmt.......CASE WHEN new_column = 1 then Something WHEN new_column = 2 then Something ELSE new_column END, then i made the sum in Table view got the results correctly.

                              new_column = Concat ( required column 1 & Required column 2 )

                              Thanks Srini for all your help