Count if

Hello,

I need a separate count of 4 specific values in 6 columns. I believe I can get one of the counts, but am not sure how to get all 4 values counted.

Formula for one of the values:

COUNT(CASE

WHEN `Item Status 1-4` = 'Active'

THEN COUNT('Item Status')

END)

After I get the values of each status, I need to get them into a chart , which I believe can do. I can probably just repeat the formula for each status, and each column, but that seems repetitive and I hope there is an easier way to accomplish this.

I can get this done in Excel but am having trouble with finding the correct way in Domo.

Any ideas out there?

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Have you tried using a pivot table card and putting the Item Status 1-4 in your row field, your date field in the columns field and then your Item Status field in the values field and choose the aggregation type of Count?

    Based on your Excel screenshot, that should work. If this doesn't work, if you can provide what your source data looks like, that would help.

  • GrantSmith
    GrantSmith Indiana 🔴

    Alternatively you can utilize an ETL to do the pivot for you as well.

    Assuming your data is in the format of:

    Date | Item Status | Count Field

    you can utilize a group by field and group by Date and Item Status taking the SUM of the count field (or alternatively if you have the raw data do a Count of that field). then toss that into a Pivot table tile to pivot your data into an output dataset.

  • mlanterma
    mlanterma ⚪️

    Hello,

    Thank you both for the answers. I think the pivot table will be easiest for me to execute. I will flag the ETL as something I need to research and learn about.


    Data looks like this:


    The Item Status column is the status of items on 1/4/21 - and needs to be included in the total evaluation. The pivot table wont allow me to have the Item Status 1/4 as a row and as a column.

    In Excel I can define the values by just typing them into a cell, and then referencing those cells for the value to count. How would I define the values for Domo to count similar to Excel?

    This is what my attempt at the Domo pivot table looks like:

    the data is not counting as Excel. Any thoughts or direction would be greatly appreciated.

  • Are you sorting the table by any fields? Sorting can cause Domo pivot tables to "drop" data. Would you mind showing the sort properties as well?

  • mlanterma
    mlanterma ⚪️

    I do not have any sorting set in analyzer.

    There are two filters, but removing them does not impact the issue. It only adds more items to each status across all status columns.



  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    couple things here.


    1) restructure your data.

    For maximum flexibility and recycleability you want the structure

    [item no], [Date], [Status], [Count Status]


    If you can't see the data at the item level then:

    [Date], [Status], [Total Count Status]


    You can accomplish this with a Collapse Columns or Dynamic Pivot in Magic ETL. Then you just need to extract the Date from the Column name. You can use Split Column with a custom delimiter " - "


    Regarding your inital Beast Mode. It's syntactically incorrect even if it's returning a result.

    COUNT(CASE
    
    WHEN `Item Status 1-4` = 'Active'
    
    THEN COUNT('Item Status')
    
    END)
    


    You generally can't have two aggregate functions (count) nested inside each other. Rewrite as:

    COUNT(
    CASE
    WHEN `Item Status 1-4` = 'Active' THEN 'Item Status'
    END
    )
    

    Ideally I would recommend avoiding COUNT unless I'm doing a COUNT DISTINCT and instead favor summing rows.

    SUM (
    CASE
    WHEN `Item Status 1-4` = 'Active' THEN 1
    END
    )
    

    The nice thing about this approach is you don't have to ask yourself "does COUNT treat text or numbers differently, what about NULLs in the Status column. etc. It's just ... either you met your CASE condition and therefore returned a 1 or you didn't.


    GLHF.

    FYI I provide mentoring and implementation services.

  • Hi @jaeW_at_Onyx

    Thank you for the terrific directions and suggestions. I was able to unpivot the data and split the dates into the column 'Status Date'. The ABC column is there because I could not figure out how to only take the 1/04/21 when splitting the original 'Item Status' in the ETL.

    Then I took your suggestion to do a Sum instead of a Count for each Status (Active, EOL, Inactive, Inactive1).

    Then used beast mode results to create a table. And this is where I am stuck again - how do I get all counts into a similar format as the original excel table? Do I have to add another WHEN condition that would tell the formula when to 'count' the status?


    Thank you again for the direction and assistance.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    so put [Staus Date] in columsn.

    put [status] in rows

    and just do a sum(1) as your metric. (remove the other metrics).

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited July 15

    if you waant the total of all active statuses you need


    sum(sum(1)) over () this will give you a grand total for all rows.
    

    if you want a grand total of only active statuses

    sum(sum 
        case when Status in ('choice a', choice b' ) then 1 end 
    )) over ()
    

    these are window functions which is a feature you'd need your CSM to enable "window functions in beast modes"