The sample data, manipulations, and such are below. My issue concerns the 4th portion with the lag function into it. The desired result would be to have the empprevyearpct (yes, I know it is actually a qtr) value show up as 4.35% instead of .04347826. I have been trying to do so using scales can't figure out how to get the number to the right of decimal and also make it into a percent.
library(readxl) library(dplyr) library(data.table) library(odbc) library(DBI) library(stringr) employment <- c(1,45,125,130,165,260,600,2,46,127,132,167,265,601,50,61,110,121,170,305,55,603,66,112,123,172,310,604) small <- c(1,1,2,2,3,4,NA,1,1,2,2,3,4,NA,1,1,2,2,3,4,NA,1,1,2,2,3,4,NA) area <-c(001,001,001,001,001,001,001,001,001,001,001,001,001,001,003,003,003,003,003,003,003,003,003,003,003,003,003,003) year<-c(2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020) qtr <-c(1,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,1,1,1,1,1,2,2,2,2,2,2,2) smbtest <- data.frame(employment,small,area,year,qtr) smbtest$smb <-0 smbtest <- smbtest %>% mutate(smb = case_when(employment >=0 & employment <100 ~ "1",employment >=0 & employment <150 ~ "2",employment >=0 & employment <250 ~ "3", employment >=0 & employment <500 ~ "4")) smbsummary2<-smbtest %>% mutate(period = paste0(year,"q",qtr)) %>% select(area,period,employment,smb) %>% group_by(area,period,smb) %>% summarise(employment = sum(employment), worksites = n(), .groups = 'drop_last') %>% mutate(employment = cumsum(employment), worksites = cumsum(worksites)) smbsummary2<- smbsummary2%>% group_by(area,smb)%>% mutate(empprevyear=lag(employment), empprevyearpp=employment-empprevyear, empprevyearpct=((employment/empprevyear)-1)) smblonger2<-smbsummary2 %>% ungroup() %>% pivot_longer(cols = employment:worksites, names_to = "measure", values_to = "value") %>% group_by(area,measure) %>% pivot_wider(names_from = period, values_from = value) %>% gt()