Best Of
Re: Creating an "unassigned" row for gantt chart visualization
I would suggest keeping your data in your original format and then building out your unassigned dates by using the rank and window tile and a formula tile. I did it by doing the following:
- Connect your original dataset to a rank and window tile and then create a column called Last End Date and choose the Lag function using the End Date column with an offset of 1. Order by the start ascending and partition on the resource name
- Add a formula tile next and create 3 columns that look like this
This will result in an output like this:
You can then filter to unassigned equal 1 and overwrite your assignment name column to unassigned and then append to your original dataset using the unassigned start and end dates as your start and end dates.
Hope that makes sense.
Re: Changing color of line in line chart
To change the color of the line or bar without it adding additional items in the legend, use the color rules but leave the condition as "always" and then choose your color. This will allow to change the colors of each.
Re: Survey - Count the answers.
You could do a case statement to look for each color in the string, but I think you will need to create a beast mode for each color. For example, the red beast mode would look like this
(CASE when `String` LIKE '%Red%' THEN 1 ELSE 0 END)
Blue would look like this
(CASE when `String` LIKE '%Blue%' THEN 1 ELSE 0 END)
You can then put each of these beast modes into your card and sum them each to get the total counts.
Re: Survey - Count the answers.
You can separate the multiple answers out into separate columns using the Split Column tile in Magic ETL. You would need to use multiple tiles to account for the maximum number of answers that a person might have selected. Once they are all separated, I would recommend adding an Unpivot tile so that you have a single column called answer and if a person had selected 3 colors, it would result in 3 rows, one for each answer. It is then very easy to count the results.
Re: Period over Period (MOM/YOY) option in Multi-Value card for 2 months prior
@NathanDorsch glad I was able to help. If you can accept any answers that helped you, that will help others in the community.
Re: Unique ID within parameter
You could utilize the Lag function in the rank & window tile get the previous row's flag next to the current row's flag as another column. Then use the formula tile to overwrite the unique id if the previous and current flag are the same. Finally, you could use the rank & window again and use the row number function and partition on the account number field.
Hopefully I understood your request correctly and that this helps.
Re: Unique ID within parameter
@user027926 you can't use a lag function in a formula tile within Magic ETL. That is why I suggested doing a lag first with the rank & window tile to put the previous value next to your current value as a new column. Once you have the previous value in the same row as your current value, you could do the formula work that you want to do.
I'm not sure if it will help (and I tend to stay away from using them regularly) but you can do window functions at the card level. It is not enabled by default, so you need to as your CSM to "enable window functions in beast modes." You are wanting to do quite a few things, so I don't know that you can do it all at the card level, but thought I would mention it in case it is helpful to you.
Re: Card Drill
@Fadem I have not had trouble saving my drill paths and the columns in my table card staying the way I designed them. Are you clicking on the wrench of the main card and choosing Edit Drill Path and then clicking Edit View and then designing your card (I assume a table card) the way you like and then choose Save and Close? That should be all that you need to do.
The final dataset table view can only be shown or hidden and all the columns in the dataset are shown.
Hope this helps.
Re: trends ratio for last 4 months
If you do have a date field, you can normalize a couple different ways to get them to be grouped by month and year. The easiest is probably using the Last_Day() function, like this (replacing dt with your fieldname that has the date:
LAST_DAY(`dt`)
This will create a date field for you and have all the transactions in a given month have the same date. You can then use this field in your rank and window and order by this field descending. Try that for starters before tackling the rank and see how it looks for you.
Re: Calculated Field - Outstanding Invoice Count with date constraints
That formula won't validate as constructed. I would re-write it like this, assuming you have a due date field:
SUM(case when `type` IS NULL and DATEDIFF(`duedate`,CURRENT_DATE()) <=14 then 1 else 0 end)
This assumes type is actually null and not an empty string. I am using sum instead of count because we need to only count certain ones and we are using ones and zeros to do that. I'm assuming the due date is beyond the current date and so we are looking to see if it is within 14 days.
Hope this makes sense.