cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Value-Names in Pivot Table

mamuewue
8 - Cloud Apps
8 - Cloud Apps

Hi folks, I am trying to name the "value" columns in a pivot table in a dynamic way.

I have a table that compares the last month data, with the data the month before. Right now, on the 6th of November, i would compare "October MRR" with the "September MRR". In the table I apply a filter which gives me the right results, all good here.

However, on the naming, rather than calling it "last month MRR", and "month before last" i would prefer to write "October MRR" and "September MRR". So, the "value description" would need to change dynamically based on the month we are in right now. 

Is there any easy way to do this? See also screenshot for clarity.

Thanks, MaMueWue 

1 ACCEPTED SOLUTION

rapidbisupport
11 - Data Pipeline
11 - Data Pipeline

Hi @mamuewue,

The widget script below changes the titles of items in the 'values panel' dynamically based on the current month. It looks for value panel items that are named 'last month MRR' & 'month before last' and replaces them with '<last month's name> MRR' and '<month before lasts name> MRR'.

 

widget.on('initialized', (w, args) => {
  const month = ["January","February","March","April","May","June","July","August","September","October","November","December"]
  
  var x = new Date()
  x.setDate(1)
  x.setMonth(x.getMonth()-1)
  
  var y = new Date()
  y.setDate(1)
  y.setMonth(y.getMonth()-2)

  const items = w.metadata.panel('values').items
  for (let i = 0; i < items.length; i++) {
	const item = items[i]
    if (item.jaql.title === 'last month MRR') {
		item.jaql.title = `${month[x.getMonth()]} MRR`
	}
	if (item.jaql.title === 'month before last') {
		item.jaql.title =  `${month[y.getMonth()]} MRR`
	}
  }
})

 

Let me know if this works for you?

Thanks,

Daniel

RAPID BI

[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

 

 

View solution in original post

2 REPLIES 2

rapidbisupport
11 - Data Pipeline
11 - Data Pipeline

Hi @mamuewue,

The widget script below changes the titles of items in the 'values panel' dynamically based on the current month. It looks for value panel items that are named 'last month MRR' & 'month before last' and replaces them with '<last month's name> MRR' and '<month before lasts name> MRR'.

 

widget.on('initialized', (w, args) => {
  const month = ["January","February","March","April","May","June","July","August","September","October","November","December"]
  
  var x = new Date()
  x.setDate(1)
  x.setMonth(x.getMonth()-1)
  
  var y = new Date()
  y.setDate(1)
  y.setMonth(y.getMonth()-2)

  const items = w.metadata.panel('values').items
  for (let i = 0; i < items.length; i++) {
	const item = items[i]
    if (item.jaql.title === 'last month MRR') {
		item.jaql.title = `${month[x.getMonth()]} MRR`
	}
	if (item.jaql.title === 'month before last') {
		item.jaql.title =  `${month[y.getMonth()]} MRR`
	}
  }
})

 

Let me know if this works for you?

Thanks,

Daniel

RAPID BI

[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

 

 

mamuewue
8 - Cloud Apps
8 - Cloud Apps

OMG, this is incredible. It works exactly as requested. Thanks so much for guidance, and explanation.