Best Of

Re: Cant get the percentage of the total!!!

Unbelievable, I had to calculate everything in ETL because of the table not working.

Power BI is looking better and better TBH

user15431

1

Parsing text with common delimiters

So I've now had a chance to reuse this solution about three times and that tells me that it is common enough that I should share it. The principle here is that you have a piece of text with a good naming convention that is delimited with a common character. For example, "Name_Date_Product_Channel" which is seperated by underscores.

This piece of SQL code I'm including below will allow you to parse through the text and pull individual elements out as columns you name. You'll edit the elements in orange, green, and blue below.

Select `your_column_here`,
-- Parse Column 1 through 4.
Case WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 1 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(`your_column_here`,'_',1)),' ','')
ELSE NULL
END as 'Name',
CASE
WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 1 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(`your_column_here`,'_',2),'_',-1)),' ','')
ELSE NULL
END as 'Date',
CASE
WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 2 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(`your_column_here`,'_',3),'_',-1)),' ','')
ELSE NULL
END as 'Product',
CASE
WHEN (LENGTH(`your_column_here`) - LENGTH(REPLACE(`your_column_here`, '_', ''))) BETWEEN 3 AND 4
THEN REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(`your_column_here`,'_',4),'_',-1)),' ','')
ELSE NULL
END as 'Channel'
FROM your_table_here

The case statments above can be extended to as many parsings as you need. If no match is found (in otherwords a null occurs in your data) it will just have a blank field.

I hope you find this as helpful as I have found it recently. If you use it and like it please upvote this entry!

Rich

1

Aggregate count function does not work in the denominator

When I use the following statement in a card summary, it works properly. But when use it as a value in a Single Value card, the count function doesn't work anymore:

SUM(CASE

WHEN ROUND(100*`Score`/`PossibleScore`)>=60 THEN 1

ELSE 0

END)

/

COUNT( `AttemptNumber`)

For more context, the Attemptnumber column has 450 rows where each row contains a number same number (e.g. 1 or 2, etc). Therefore, I'm expecting to count 450 of them and get 450 in the denominator. But it ends up with the value of only one of the samples (e.g. 1 or 2, etc). I hope I explained it clearly.

Oddly enough, when I changed the division operation to addition it works! Any suggestion and comments are highly appreciated.

Thanks,

Musetti

AMousavi

1

Re: Schedule Dashboard To Include CSV Attachments

@DashboardDude Thank you! Yeah sadly those were the same options I was coming up with as well that you listed in your loom. There are some days that we send this customer multiple emails, containing several csv's each, so I was trying to avoid flooding them with 8 emails each with a separate csv. Thank you for your help!