Best Of

Re: Identify a duplicate

SUM(SUM(1)) OVER (PARTITION BY `loan_id`)

This window function beast mode will count the total number of records seen with the same loan_id value. You can compare it to see if it's greater than one to determine if it has a co-borrower.

GrantSmith

1

Re: Get Top 5 category values for each date from category

You can do this by using the rank window function in a beast mode and then adding it to the filter to filter where rank is less than or equal to 5.

RANK() OVER (PARTITION BY WEEKOFYEAR(`date`) ORDER BY SUM(`metricfield`) DESC)

If you don't have window functions for beast modes turned on in your instance, you will need to have your CSM enable it.

MarkSnodgrass

1

Re: Have date range in x axis for week

@chetan_cricut You would need to create a calculated field to concatenate the first and last day of the week (similar to how you created the week start date) for your x-axis, sorting by week start date. Your x-axis calculation would look something like this:

concat(date_format(`Date` - dayofweek(`Date`) + 1,'%b %d),
' - ',
date_format(`Date` + (7 - dayofweek(`Date`)),'%b %d))

MichelleH

3

Writing Beast mode with Data label of Measures

Hi All,

I have three measures - Open qty, onhandunits and openpoqty... When they are brought into the table in visualizer they are brought in as sum(Open qty) , sum(onhandunits ) and sum(openpoqty) and then given different data label names

Now I am trying to create a filter using the condition

case when sum(open qty)> sum(sum(onhandunits),sum(openpoqty)) then 1 else 0 end...Can you please let me know how to do this in beast mode

ozarkram

1

Re: Card filtering in analyzer

Have you tried using a COALESCE and filtering on the default value?

COALESCE(`field`, 'Empty')

GrantSmith

2

Re: Workaround to Pivot tables pushing non-valued categories to the bottom.

@Stuck Can you elaborate on how you "force sorted"? You can create a beast mode to create a custom sort order like below:

case 
when X then 1
when Y then 2
when Z then 3
else 4 
end

Also, can you check that the aggregation used in your sort fields aligns with the aggregation of the same fields in the pivot table? If any of your sort values are set to "No aggregation", then that could be causing issues as well.

MichelleH

2