ColinHaze Member

When I tried that idea (making all the lines a comment besides the one I wanted to test) none of them had the correct format. I even tried taking it out of the case statement and taking CONCAT(ROUND(100*(1 - (SUM(PUPSTOPCOUNT_LATE) / SUM(PUPSTOP_COUNT))),0), '%') but the % would not show up.

I guess you had to be looking at the dashboard to see the names of the columns. Thank you for your help!

jessdoe That's closer that what I'm looking for. The only thing I have a question on is how do I get the months names in there? Here is what I currently have. Instead of Chart I would like Previous month(above the second Column) and current month(above the third). I tried to switch the column name under column definition…

I essentially just want the total row to be the difference between the two.

I dont know if this might be the cause of the issue but the DATALLINAVG is calculated in the ETL using a formula tile. The formula for that is PC-Miler Practical Mileage*(Contract Avg Linehaul Rate+Contract Fuel Surcharge)

marcel_luthi I thought so too. When I use the formula you provided I get a chart that looks like this.

Removing all sorts and filters (while removing the series) results in the same problem.

ColemenWilson No filters, And sorted by date field.

MichelleH If I were to select the card and select 'today' in the date range filter and save it. When I select any other day on the radio card it says 'No data in this range' however, when I select this month for the date range on the card then select a date on the radio filter it shows data.

MichelleH Thats the first thing I tried but I am trying to allow the card to go back in time by using this date filter so that we can see pervious day data.

I am just creating this filter to only filter a singular card on the dashboard so the global date filter wont work sadly.

ST_-Superman-_ The value I want the chart to display is the CPM(which it is doing correctly) The problem comes in with the hover text. Here is my logic using a simple example. For the month of 7/1/2023 Kinard has 34 loads The other companies like kkw, power logistics, etc… have different # of loads. Lets just say all the…

Thank you for trying! Ill keep playing around with it!

SUM(Carrier Pay) / SUM(Total billed miles)

That didnt seem to work. Looking at Kinard for 7-1-2023 that beastmode gave me 17%

Here is the graph. I am trying to get 32.71% (which is in the piviot table) to show in the first tier when I hover over Kinard. Concentration is just the % of subtotal in the pivot table.

jessdoe I am having a hard time creating this beastmode. The beastmode I am using is SUM(Loads) OVER (PARTITION BY CARRIER_NAME) / SUM(Loads) What could I be doing Wrong?

GrantSmith Yes exactly , I am trying to use option 2 you mentioned. In the formula tile I assume I would use a case statement and then use between to get the dates then assign them to a month, correct?

Amazing, thank you!

For some reason I am getting 1.3Billion Output rows. When I put it in a table I am getting 4,177 consecutive days for all the products and 1.3billion for most consecutive days for all products

Can you explain the two rank and window tiles a little more? I seem to be getting stuck at this step.

@pauljames No, I haven't but that seemed to have solved my issue! Thank you.

That was my original thought as well but I noticed our first order of SEP was on the 5th so I had to switch the -1 around to - 3. That seems to work.

Hey Mark, Another quick question. I am unable to change the range of the date field to the prior month. Would I need to do another date_sub and interval it by one month?

Worked perfectly, thanks again Mark.

I will give that a try, thank you.

That solved it. Thank you

When I drop the 'OVER (PARTITION BY LASTDAYOFMONTH) I get stuck with this "This calculation contains an error." SUM(CASE WHEN SUM(Loads) * (SUM(carrier pay)/ SUM(Loads)-Dat Avg CPL) > 0 THEN SUM(Loads) * (SUM(carrier pay)/ SUM(Loads)-Dat Avg CPL) Else 0 END)

Here is the beastmode that is being used SUM(CASE WHEN SUM(Loads) * (SUM(carrier pay)/ SUM(Loads)-Dat Avg CPL) > 0 THEN SUM(Loads) * (SUM(carrier pay)/ SUM(Loads)-Dat Avg CPL) Else 0 END)OVER (PARTITION BY LastdayofMonth) And ive also removed all the filters. I am still getting this.

That solved the issue. I knew it was a simple mistake and I couldn't figure it out. Thanks for teaching me something new.