Golfoholic Member

@MarkSnodgrass Each column is a MTD or a prior month calc. They have been written using a custom beast mode like: SUM(CASE WHEN `Description` like '%1_Move-Ins_sDescActivity%' AND MONTH(`Activity_Date`)=MONTH(CURDATE()) AND YEAR(`Activity_Date`)=YEAR(CURDATE()) then `Value` END) so that each columns only shows the intended…

@MarkSnodgrass shoot it should be activity date, i was toying around with it cause there is a current_date column as well. a little background on this dataset. It runs daily so we get a current_date like 3/14 and activity date that aggregates all the previous months and has a format like 2/1/2022. I am doing a bad job…

Attempted trying to do it with this beast mode, this is like the ones used for the other MTD columns here but I assume I am not getting the results I am looking for because it isn't exactly MTD it's 30 days, and adding the two together adds another layer of complexity. SUM(CASE WHEN `Description` IN ('0_0-10_Period',…

Ok tiebreaker! @MarkSnodgrass @GrantSmith this will move the original goal post a bit. This is trying to be used in a table. On the date filter i have it open to all time and the columns are MTD or prior month. Calculated using beast modes. How can I add the two periods above for the most recent. which would be 3/14. Using…

@MarkSnodgrass Brilliant. Thank you!

@MarkSnodgrass Son of a gun you are right. The dataset has a ton of columns and I wasn't looking for the new column to be at the end of the columns. I was just referring to the existing column. Thanks!

@GrantSmith Now the more difficult question is where is that and why can't I see it in the summary number drop down detail?

I figured it out. By switching to a pivot table it then gives you more flexibility with column, values, and especially the date filter.

@MarkSnodgrass Ah ok, that did work! Just to be sure i'm on the same page looking at these groups, lets take 30 days late - would this take those that are 30 days late or less or would it take those that are between 30 days and 59 days late?

@MarkSnodgrass Last twist to it. Now what I am wanting to do is break these out so that i could have a count for each in said group so for those with in 30 days late and those that have a date with in the coming 30 days. then so on so forth for the other groups. 60 days to 31 late 31 to 60 days upcoming. PL&TY!!!! I would…

@MarkSnodgrass Adding on to this. That worked like a dream. Now would it be possible to add a group of future dates to the same beast mode so they all appear in a single column? So could i add another 2 groups where the Auction Date is 30-59, and +60 days in the future? So looking at everything that is out a month or more.

@GrantSmith We don't have that number calculated. What I am trying to see is from the Auction date that we do have. How many fit in those groups between that auction date and today.

@GrantSmith just looking at what you have above, it makes sense and is doing what I am looking. Only thing that would need to be adjusted is that in this example it is for the entire month of November. So, that start date would be 11/1/2021.

@amehdad @MarkSnodgrass Ok I got it figured out. For reference to anybody that might come across this in the future. when you look at the dataset there will be a button where you would go to manually run the dataset that will ask to 'resume' click that, then that same button will say 'run' and you can run manually. Why it…

@GrantSmith Works like a dream. Thank you!

@GrantSmith @MarkSnodgrass @jaeW_at_Onyx You guys are awesome. I appreciate all the insight!

@jaeW_at_Onyx @GrantSmith Everything is looking good. The counts that I was looking for are reflecting correctly. There is just one thing that is a little off. I see a good amount of nulls which I expected, but what is weird is there are nulls and then there are zeroes. I would want those that are showing 0 to show reflect…

@MarkSnodgrass Looks to be working much better. Only thing I did different was on the date filter instead of last 12 months, I used last 720 days just to have an overly wide range of data pulled in then filtered to 'Last 15 Days' My last question is my YOY % beast mode. There seems to be a lot of unexpected variance. What…

NOTE: the description column has the names of many different metrics, and the value column has those values of the different metrics. To single out multiple individual metrics to display on the same visualization have had to use a beastmode such as: SUM((CASE when `Description` like '%Net%' then `Value` END))

@MarkSnodgrass Ok I will have to give that a try. My next question for this approach would be how does the append approach handle duplicated rows? Most rows will have a duplicate in the both datasets. Will it show the duplicates on separate rows, or will it recognize the duplication and eliminate one while also keeping the…

@GrantSmith If I wanted to pull out the first 2 numbers in the column to created a 'Disc%' column would the operation = 'substring' and the the start position 2 and substring length 2 as well?

@GrantSmith is that also possible within the ETL, or would it have to take place in the system where this data originates?

it is configured as string. Illustrates the date someone is scheduled to move-out. the minutes/seconds is kind of pointless. Is implemented in the report on the side where this data comes from prior to getting loaded in to Domo.

bRentable is the column name*

@MarkSnodgrass It is only that daysofweek beast mode, but it is aggregated 'Sum' in the filtering field I'm not sure if that has any influence. I just saw it and thought it was peculiar.

@GrantSmith it is a left outer join. I took the value from the data set and copied it to the webform in the appropriate column, and that is the column in both that I am joining on.

@jaeW_at_Onyx This is the first step to create a data set that will house many columns of daily KPI's that are most frequently used aggregated at a store level. To start, I have a webform that has unique identifiers for each site from every different source we pull data from, unfortunately every system we use, uses a…

@MarkSnodgrass By doing this will it also aggregate the data in the movein and moveout columns? so that for example 3196 would go from looking like this: To:

@GrantSmith Yes, essentially have a date column in a different format. Batch last run, along with a Month Day Year format. @MarkSnodgrass By doing this in ETL, would I have to pull in the Date operations tile to set the column type or is that something I could just go in, click something on the column and select what…

@MarkSnodgrass The data is coming in through an email connector in an xlsx format.