Beast mode for count distinct by concatenating two columns

ozarkram · 2022-05-10T21:57:18+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Beast Mode

Formulas

Comments

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

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

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

mhouston

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

ozarkram

@mhouston : Sorry for not being clear..I got the answer to this here

https://dojo.domo.com/discussion/54893/help-with-beast-mode-for-getting-the-distinct-count-by-order-number-for-this-year-and-last-year