create custom measure on a dashboard

[ramansingh89]

Hello,

I would like to create a custom measure on Sisense dashboard that can do the same this SQL is doing

SELECT
    COALESCE(SUM(CASE
        WHEN channel = 'Email' AND message_event_type = 'Dispatched' THEN count_of_engagement
        ELSE 0
      END
    ), 0) AS email_count_dispatched
FROM event;

Can anyone guide me on how to proceed with this?  

@DRay 

[ramansingh89]

I am trying to follow this doc here: https://docs.sisense.com/main/SisenseLinux/creating-formulas-based-on-criteria-and-conditions-filter...

but it is giving me error (screenshot attached)

[HamzaJ · Accepted solution]

Hey @ramansingh89 

It would be something like this;

CASE
        WHEN (SUM(count_of_engagement),channel,message_event_type)>0 THEN sum(count_of_engagement)
        ELSE 0
      END

If you want to sum this, then you probably would need a multi-pass aggregation within this formula. If you can share a screenshot of your widget I can help you finetune this. above formula will work (will work the best on a detailed level).

Hamza