This discussion is archived
11 Replies Latest reply: Feb 4, 2013 5:05 PM by KBabu RSS

Summation Issue - Table View

KBabu Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    I sent mail to your ID explaining the issue...
  • 9. Re: Summation Issue - Table View
    KBabu Explorer
    Currently Being Moderated
    Hi Sreeni

    Did you got my Mail....Could you please help me ?
  • 10. Re: Summation Issue - Table View
    KBabu Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points