Help with Beast mode for getting the distinct count by order number for This year and last year

ozarkram
ozarkram Member
edited March 2023 in Beast Mode

Hi All,

Hope you are doing well!.. I am trying to get the count of distinct combination of ordno and partno by ordate ..Please find the input table below:


image.png


The output table that I am trying to get to is

image.png


Can you please help me with the beast mode for this..Also attached the excel file...

Sample Sheet.xlsx




Thanks,

Arun

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @ozarkram

    I'd recommend configuring a date dimension dataset to easier track YOY differences / comparisons. I've done a write up on this topic in the past which you can find here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest


    You can then utilize a beast mode to count the distinct combinations. I'm concatenating 'ordno' and 'partno' strings to the values in the off chance order number and part number potentially overlap (i.e. O# BC12 / P# 213211 vs O# BC122 / P# 13211 would be the same values even though they're separate)

    COUNT(DISTINCT CASE WHEN `Period Type` = 'Current' THEN CONCAT('ordno', `ordno`, 'partno', `partno`) END)



    COUNT(DISTINCT CASE WHEN `Period Type` = 'Last Year' THEN CONCAT('ordno', `ordno`, 'partno', `partno`) END)


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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @ozarkram

    I'd recommend configuring a date dimension dataset to easier track YOY differences / comparisons. I've done a write up on this topic in the past which you can find here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest


    You can then utilize a beast mode to count the distinct combinations. I'm concatenating 'ordno' and 'partno' strings to the values in the off chance order number and part number potentially overlap (i.e. O# BC12 / P# 213211 vs O# BC122 / P# 13211 would be the same values even though they're separate)

    COUNT(DISTINCT CASE WHEN `Period Type` = 'Current' THEN CONCAT('ordno', `ordno`, 'partno', `partno`) END)



    COUNT(DISTINCT CASE WHEN `Period Type` = 'Last Year' THEN CONCAT('ordno', `ordno`, 'partno', `partno`) END)


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

    Hi @GrantSmith : Thank you so much for your response! I am stuck at this step :


    This code will work for Magic 1 or Magic 2. Just cope the code in the code block below and then use Command+V (Mac) or Control+V (Windows) to paste the code into your Magic ETL palette. You will need to define the input dataset and the output dataset.

    {"contentType":"domo/dataflow-actions","data":[{"name":"[DIM] Calendar Dates","id":"3cc1f500-f11b-495e-b563-e2b8422626a1","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Current Date","id":"551fbad1-b100-44da-8ade-4ef2243b1e75","type":"DateCalculator","gui":{"x":252,"y":180},"dependsOn":["1662a58a-fc68-4d7b-a981-11e4a546db6a"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Current","id":"1662a58a-fc68-4d7b-a981-11e4a546db6a","type":"Constant","gui":{"x":144,"y":180},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Current"}]},{"name":"Last Week Date","id":"a7b6768c-2b7d-4c88-ab83-c3bbe8fff0c3","type":"DateCalculator","gui":{"x":252,"y":276},"dependsOn":["0b91dd40-bdf0-4068-9ce0-258ec568138c"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"7","type":"LONG"},"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Last Week","id":"0b91dd40-bdf0-4068-9ce0-258ec568138c","type":"Constant","gui":{"x":144,"y":276},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Week"}]},{"name":"Last Month Date","id":"e0a38130-cf41-4056-858a-7825162ebf63","type":"DateCalculator","gui":{"x":252,"y":372},"dependsOn":["aafe8e71-83d5-483b-8bb7-626bdf0e6eb8"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Month","id":"aafe8e71-83d5-483b-8bb7-626bdf0e6eb8","type":"Constant","gui":{"x":144,"y":372},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Month"}]},{"name":"28 Days Ago Date","id":"4d7d621c-4491-48bd-99d4-e1e7f4a748e7","type":"DateCalculator","gui":{"x":252,"y":468},"dependsOn":["0037e619-4cae-4bdc-82f8-7b6ed8fb7710"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"28 Days Ago","id":"0037e619-4cae-4bdc-82f8-7b6ed8fb7710","type":"Constant","gui":{"x":144,"y":468},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"28 Days Ago"}]},{"name":"Last Year Date","id":"e275d916-e4f0-454e-85e8-f1469c8ed0df","type":"DateCalculator","gui":{"x":252,"y":564},"dependsOn":["b603d5e9-2d37-4254-a973-280c32bfe867"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Year","id":"b603d5e9-2d37-4254-a973-280c32bfe867","type":"Constant","gui":{"x":144,"y":564},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Year"}]},{"name":"Last Year DOW Date","id":"584b70ed-7b8c-43b5-a417-bfa60ea324b0","type":"DateCalculator","gui":{"x":252,"y":660},"dependsOn":["e8ab3aef-24de-4110-be60-67f47d549518"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"364","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Year DOW","id":"e8ab3aef-24de-4110-be60-67f47d549518","type":"Constant","gui":{"x":144,"y":660},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Year DOW"}]},{"name":"2 Years Ago Date","id":"8c74ae9d-7b61-49d1-9b8b-a5e091166a8a","type":"DateCalculator","gui":{"x":252,"y":756},"dependsOn":["296ce080-f4ab-4478-8bef-ec943e8457cd"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"24","type":"LONG"},"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"2 Years Ago","id":"296ce080-f4ab-4478-8bef-ec943e8457cd","type":"Constant","gui":{"x":144,"y":756},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"2 Years Ago"}]},{"name":"2 Years Ago DOW Date","id":"62391242-890e-4ffb-a841-a01c9e981f5a","type":"DateCalculator","gui":{"x":252,"y":852},"dependsOn":["49f8ca7e-922f-48c0-8033-010a6c76cf5b"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"728","type":"LONG"},"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"2 Years Ago DOW","id":"49f8ca7e-922f-48c0-8033-010a6c76cf5b","type":"Constant","gui":{"x":144,"y":852},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"2 Years Ago (DOW Aligned)"}]},{"name":"Last Quarter Date","id":"4a3ad517-7717-4ec3-89d6-96d8da12d9c8","type":"DateCalculator","gui":{"x":252,"y":948},"dependsOn":["3640e331-0c91-434f-b1a7-37b81158dfa9"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"3","type":"LONG"},"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"Lat Quarter","id":"3640e331-0c91-434f-b1a7-37b81158dfa9","type":"Constant","gui":{"x":144,"y":948},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Quarter"}]},{"name":"Append Rows","id":"46436b73-0ad7-43f8-b2db-cf5d06c5a545","type":"UnionAll","gui":{"x":540,"y":564},"dependsOn":["551fbad1-b100-44da-8ade-4ef2243b1e75","a7b6768c-2b7d-4c88-ab83-c3bbe8fff0c3","e0a38130-cf41-4056-858a-7825162ebf63","4d7d621c-4491-48bd-99d4-e1e7f4a748e7","e275d916-e4f0-454e-85e8-f1469c8ed0df","584b70ed-7b8c-43b5-a417-bfa60ea324b0","8c74ae9d-7b61-49d1-9b8b-a5e091166a8a","62391242-890e-4ffb-a841-a01c9e981f5a","4a3ad517-7717-4ec3-89d6-96d8da12d9c8"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_ALL","strict":false},{"name":"Select Columns","id":"4ff2d5d9-25e0-4d7a-b745-6ce907fced89","type":"SelectValues","gui":{"x":647,"y":564},"dependsOn":["46436b73-0ad7-43f8-b2db-cf5d06c5a545"],"removeByDefault":false,"notes":[],"fields":[{"name":"dt","rename":"Report Date"},{"name":"Period Type"},{"name":"Comparison Date"}]},{"name":"Dates with Offsets","id":"ef556540-dacb-465c-ba0c-b2b4f614b902","type":"PublishToVault","gui":{"x":756,"y":564},"dependsOn":["4ff2d5d9-25e0-4d7a-b745-6ce907fced89"],"removeByDefault":false,"notes":[],"dataSource":{"name":"Dates with Offsets"},"partitionIdColumns":[]}]}

    When I try to copy the above in the clipboard nothing happens..Can you please help here..

  • ozarkram
    ozarkram Member

    Thank you so much @GrantSmith !..Really appreciate your help!

  • German_Rodriguez
    German_Rodriguez Member
    https://dojo.domo.com/main/discussion/comment/57778#Comment_57778

    hi, i am stuck in this step too, where should I paste the code?

  • German_Rodriguez
    German_Rodriguez Member
    https://dojo.domo.com/main/discussion/comment/57778#Comment_57778

    hi, i am stuck in this step too, where should I paste the code?

  • Viet
    Viet Member

    When you have Magic ETL open, you dont need to select any tile. Just press paste and the code will automatically add all of the tiles for you.

Categories