Problems with Date Format and R Script

[Anonymous]

Hello everybody,

I'm not getting the result I need, when I try to build a small GANTT-like Chart via R in PBI.

See the code as following:

`dataset` = read.csv('C:/Users/cgmeiner/AppData/Local/Radio/REditorWrapper_00f11c68-b4d0-4572-9e0d-c4ba7e30421c/input_df_9a681b40-b3ce-4e89-aed9-0fb6c51643f0.csv', check.names = FALSE, encoding = "UTF-8", blank.lines.skip = FALSE);
library(ggplot2) # Visualization
library(date)

#lct <- Sys.getlocale("LC_TIME"); Sys.setlocale("LC_TIME", "C")
#Sys.setlocale("LC_TIME", lct)

#Start3 <- as.date(paste(substr(dataset$Start,6,7),"-",substr(dataset$Start,9,10), "-", substr(dataset$Start, 0, 4)))
#Ende3 <- as.date(paste(substr(dataset$Ende,6,7),"-",substr(dataset$Ende,9,10), "-", substr(dataset$Ende, 0, 4)))

ggplot(dataset,aes(x=as.date(paste(substr(dataset$Start,6,7),"-",substr(dataset$Start,9,10), "-", substr(dataset$Start, 0, 4))),
                   y=dataset$Hauptkasse_Name, color=PZN_Company)) +
  geom_segment(aes(x=as.date(paste(substr(dataset$Start,6,7),"-",substr(dataset$Start,9,10), "-", substr(dataset$Start, 0, 4))),
                   xend=as.date(paste(substr(dataset$Ende,6,7),"-",substr(dataset$Ende,9,10), "-", substr(dataset$Ende, 0, 4))),
                   yend=dataset$Hauptkasse_Name),size=10) +
  scale_colour_discrete(guide=guide_legend(override.aes=list(size=10))) +

ggtitle("Tender Duration") + xlab("") + ylab("") + theme_bw()+
 

scale_x_date(date_breaks = "1 month") +
 
theme(axis.text.x = element_text(angle = 90)) 

For some reasons, the Start/Ende fields are defined as date in PBI, but as soon as the fields hit R they turn into either a factor or a character. Now I created the workaround above. Still I get the following error message:

"Error: Invalid input: date_trans works with objects of class Date only".

This is related to scale_x_date function. I'd be happy for any support or tipps from all of you.

many thanks in advance

Christoph

[v-yulgu-msft]

Hi @Anonymous,

In your scenario, since Start and End fields are both set to date format, why did you split and combine string via "paste(substr(dataset$Start,6,7),"-",substr(dataset$Start,9,10), "-", substr(dataset$Start, 0, 4))"?

Could you get expected output running above code in R tool rather than in Power BI? Besides, please share your data source .csv file so that I can test for you.

Here is an example that generate Gantt chart using R script, please see:

Gantt chart using R

Regards,
Yuliana Gu

[Anonymous · Accepted solution]

Thanks for your reply and the link for another way for a GANTT Chart in R.
In the meantime I solved my issue. When using datetime format instead of date format everything is working.
Thanks and br