Count if

mlanterma
mlanterma Member
in Charting

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.

image.png

Any ideas out there?

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • mlanterma
    mlanterma Member

    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:

    image.png


    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:

    image.png

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

  • ST_Superman
    ST_Superman Domo Employee

    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 Member

    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.


    image.png


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • mlanterma
    mlanterma Member

    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.

    image.png

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

    image.png

    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?

    image.png


    Thank you again for the direction and assistance.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    so put [Staus Date] in columsn.

    put [status] in rows

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited July 2021

    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"

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories