Can I create a calculated field based on controls?

Let’s say I have a field in a dashboard, called Sales. I have a date filter on said field for December of 2022. Can add a new column, which would be Sales again, but have it looking at the control date filter of December and look at the month prior?

I need to ensure the new sales column adjusts when the date control at the top of the page changes. Is this possible?

Hi @ryanalbanamazon

I understand you are looking to show Sales filtered for a specific date compared to Sales for Dec 2022. Can you provide more context on how you want to visualize it?

Are you looking to compare the sum of sales for selected month in a table? Or are you looking to compare Sales for Dec vs Nov 2022?

Best,
Raj

1 Like

Hi Raj! This would simply be in a table viz - columns would be Sales (based on the control) and Sales (1 month prior to the date selected in the control)

Hi @ryanalbanamazon

yes, it can be easily done with one Date Parameter and 2 calculated fields. Here are the steps:

  1. Create a Date Parameter, call it ‘SelectDate’

  2. Create a calculated field called ‘Sales this Month’ with the following formula:

      ifelse(dateDiff(Date,${SelectDate},"MM") = 0, {Sales}, 0)
    
  3. Create another calculated field called ‘Sales Previous Month’ with the following formula:

      ifelse(dateDiff(Date,${SelectDate},"MM") = 1, {Sales}, 0)
    
  4. Create a Pivot Table with Sales (optional), Sales This Month and Sales Previous Month as Values. It should look like this:

  1. Go to the Date parameter and Add a control on the page. It will act as a date filter for your visual.

For more info please check this community post

Hope it helps,
GL