Beast mode for count distinct by concatenating two columns

ozarkram
ozarkram ⚪️
edited May 10 in Dataflows

Hi All,

I am trying to do the following beast mode operation:


COUNT(DISTINCT (CASE when `ord_date` < CURDATE()-1 then CONCAT(`sales_no`,`part_no`) end ))

The expectation is the distinct combination of sales_no and part_no ..But I am not getting that..Can you please help me here..



Thanks,

Arun

Comments

  • ozarkram
    ozarkram ⚪️

    Hi All, Can you please help me here..The expectation is the distinct count of the combination of sales no and part_no ..But I am not getting the distinct count...can you please help me here..

  • sfiene
    sfiene ⚪️

    I only just started using Domo not too long ago, but I have worked with dates before. Have you tried using ADDATE(ord_date, interval -1 day) instead of CURDATE() - 1. If my understanding is correct, if you use a negative number this function will subtract that number from the date field given.

    It would look like this:

    COUNT(DISTINCT (CASE when `ord_date` < ADDATE(CURDATE(), interval -1 day) then CONCAT(`sales_no`,`part_no`) end ))

  • ozarkram
    ozarkram ⚪️
    edited May 11

    @sfiene ...Appreciate your response...But I am not able to see any change to my output...

  • @ozarkram it would be helpful if you provided an example of your dataset and the output you are getting from your beast mode, as well as what you are expecting to see.