How to show a grouped Column with a CASE logic

IgneP
IgneP Member
in Beast Mode

Hi, could smb help me on the issue below please:

the below CASE model for 'Royalty add on' is splitting my ORGANIZER 'Live Nation Italia' into 3 columns creating splits also for Autorization and Shipping exp. The idea is to have a sum of all these values and see only 1 column for Live Nation Italia.

I know for sure that the problem is Royalty add on CASE, because if I remove it I see Autorization and Shipping exp for only 1 column. hopes it makes sense.

p.s Also tried, ELSE 0 - but this gives me a 0 instead of the empty cell.

CASE is for : to see the value ONLY Collector tickets for LIVE NATION group organizers:

Live Nation 2 s.r.l.

Live Nation 3 s.r.l.

Live Nation Italia s.r.l

Comcerto Srl

a Collector ticket is the one that has

amCollectorPrice > 1

—

CASE
WHEN (nmOrganizer ='Live Nation 2 s.r.l.' AND amCollectorPrice > 1) OR (nmOrganizer = 'Live Nation 3 s.r.l.' AND amCollectorPrice > 1) OR (nmOrganizer ='Live Nation 6 s.r.l.' AND amCollectorPrice > 1) OR (nmOrganizer ='Live Nation Italia s.r.l' AND amCollectorPrice > 1) OR (nmOrganizer ='Comcerto Srl' AND amCollectorPrice > 1) THEN (SUM(qtTicket)*2.54)
ELSE ''
END

image.png

Answers

  • ggenovese
    ggenovese Contributor

    Can you give this a try? 

    SUM(
    
CASE
    
WHEN 
amCollectorPrice > 1
AND nmOrganizer IN (
'Live Nation 2 s.r.l.'
, 'Live Nation 3 s.r.l.'
, 'Live Nation 6 s.r.l.'
, 'Live Nation Italia s.r.l'
,'Comcerto Srl'
)
    
THEN qtTicket
    
END
    
)*2.54

  • Manasi_Panov
    Manasi_Panov Contributor

    Hi @IgneP,

    It should also work with nested CASE statements (CASE inside a CASE), but definitely I agree that wrapping CASE in SUM(), as @ggenovese demonstrated, is the best approach.

    SUM(CASE WHEN…) *2.54

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories