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

ozarkram

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...

Sample Sheet.xlsx

Thanks,

Arun

GrantSmith

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)

GrantSmith

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)

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

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

German_Rodriguez

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

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

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.