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

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:



The output table that I am trying to get to is


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




Thanks,

Arun

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Indiana 🥷
    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

  • ozarkram
    ozarkram ⚪️

    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 ⚪️

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