Parameter Filer

Anna_Yu

Hello everyone,

I wonder if someone has successfully figured out how to create a parameter filter in DOMO. I want to create a filter that allows dashboard readers to choose which two years they want to compare (e.g. 2021 vs. 2019 or 2022 vs. 2024). Thank you very much.

Best,

Anna

ColemenWilson

Yep, so basically you would join your data 1=1 so that it looks like this:

Then in your card you have your two values:
`data` and `comparison data`

Users select a year and a comparison year and the data for each will be displayed. I would suggest setting default values for each, for example: 2022 vs 2023 and then from there allow users to change the dates.

MichelleH

@Anna_Yu Do you always want the values to be red and black? If so, you can create two color rules so Year 1 is always red and Year 2 is always black. Here is a KB article on how to set up color rules: https://domo-support.domo.com/s/article/360043428813?language=en_US

MichelleH

@Anna_Yu You will also need to create beast modes for each year selected, like the ones in my original post. Those beast modes are what the color rules will be based off of, so if you named them differently that is what you will need to look for in the drop-downs

ColemenWilson

Could you provide a bit more information? This is possible, and I have done this - but I want to better understand the type of content you have on your dashboard and how you plan to compare one year vs another visually.

The approach I took was to build a dataflow that had comparison data self-joined to the data and then had a filter to allow end users to select the 2 periods they want to compare. With a bit more information I'll know if this approach could work for you.

MichelleH

@Anna_Yu This sounds like a good use case for Variables. I'd suggest creating two variables to select the years you want to compare. Here are some sample beast modes you could use with these variables:

Year 1 Total:

sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end)

Year 2 Total:

sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)

Variance:

sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end) 
- 
sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)

% Variance:

(sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end) 
- 
sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end))
/ 
sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)

Anna_Yu

Hello Colemen,

Thank you for taking my questions.

Here is my example. I have three columns in my dataset: number of sales, profits, and year. I created profits per item (profits/sales) in beast mode. I have used the "multi-value columns" card to show my readers the differences between the two years (e.g. profits per item 2023 vs. 2022). My challenge here is my readers want to choose the years by themselves (e.g. profits per item 2023 vs. 2021), so I think a parameter filter can solve this.

Hope I explained well. Thank you!

Best,

Anna

ColemenWilson

Yep, so basically you would join your data 1=1 so that it looks like this:

Then in your card you have your two values:
`data` and `comparison data`

Users select a year and a comparison year and the data for each will be displayed. I would suggest setting default values for each, for example: 2022 vs 2023 and then from there allow users to change the dates.

Anna_Yu

Thank you, Michelle and Colemen. :)

I have created the 'parameter filter' with variables. Now, I am facing another issue: the colours. Is it possible to automate the colour rules? For example, when someone uses 2021 to compare with 2019, all the 2021 data are marked as red and 2019 as black, and when someone uses 2022 to compare with 2019, all 2022 data are marked as red instead, and 2019 remains black.

Thank you very much!

Best,

Anna

MichelleH

@Anna_Yu Do you always want the values to be red and black? If so, you can create two color rules so Year 1 is always red and Year 2 is always black. Here is a KB article on how to set up color rules: https://domo-support.domo.com/s/article/360043428813?language=en_US

Anna_Yu

Hello Michelle, @MichelleH

My Year 1 and Year 2 are variables, so they can be any value, but yes, I want my Year 1 always be red and Year 2 always in black. I checked the colour rule settings but don't see my variables listed there. Thank you!

Best,

Anna

MichelleH

@Anna_Yu You will also need to create beast modes for each year selected, like the ones in my original post. Those beast modes are what the color rules will be based off of, so if you named them differently that is what you will need to look for in the drop-downs

Anna_Yu

Hello Michelle, @MichelleH

The beast mode is shown as numeric, so in the colour settings, I have to put a number in the condition (as below). If I put a number in it, it won't be dynamic anymore. Please let me know if I understand you correctly. Thank you.

Best,

Anna

MichelleH

@Anna_Yu You can leave the "is always" condition and the color will apply regardless of the value

Anna_Yu

Hello Michelle, @MichelleH

I left the condition blank, but the colour does not appear on my chart. Thank you!

Best,

Anna

MichelleH

@Anna_Yu Is the `current_year` field actively used in your chart? Color rules only apply to fields that are also displayed in the chart.

Anna_Yu

Hello Michelle, @MichelleH

It is not in my chart. My Y-Axis is category, X-Axis is sales, and SERIES is year. Thank you!

Best,

Anna

MichelleH

@Anna_Yu Try this instead:

• Y-Axis: Category
• X-Axis: Year 1 (Beast Mode)
• Series: Year 2 (Beast Mode)

Anna_Yu

Hello Michelle, @MichelleH

It is working now 😀 Thank you so much!

Best,

Anna