I am looking to make a line chart for a single variable that is filtered by two different fields based on drop down lists. Each of the two drop down lists has a categorical number of options that can be chosen by the user, and for the options chosen, I would like my line chart to display a separate line for each combination of options between the two drop down lists. E.g. if drop down 1 has options A, B and Drop down 2 has 1, 2, 3 I would like to have a line chart that has filters my target variable by A1, A2, A3, B1, B2, B3
I’m new to QuickSight so any specific details and instructions on how to calculate this would be greatly appreciated!
Hi @ZacB and welcome to the QuickSight community!
Off the top of my head, we should be able to put together a calculated field using the contains function depending on your data.
What are your data fields available that are being used for this and what format are they in? What is the full available value range as well that you have for each field so that we know how many options we are dealing with (A-?, 1-?).
My data fields that I’m using for the drop down lists are State, which has options 1-6 and Team, which has options 1-4. I’ve made both of these options as strings for the time being because I wanted them as categories rather than numbers.
On my x-axis I have the variable Time (months) (which I think it’s a date format) and on my y-axis (value axis) I have Profit which is a float variable.
In this case, I would like my line chart to be able to display the profit for (1,1), (1,2),…(1,4), … , (6,1), … , (6,4) based on which options the user would like to see. Let me know if there’s anything that I missed.
Hi @ZacB,
Apologies for missing your last response as I did not get notified of a response without the tag!
So then I assume that you have a separate field that has values assigned to each combination?
In terms of calculated fields, this would be dependent on if you’re using a multi-select or single select parameter.
For a multi-value param (checking for NULL in param value is checking for select all):
ifelse(
(in(substring({dataset_field}, 1, 1), ${ParamA}) OR in(NULL, ${ParamA})) AND
(in(substring({dataset_field}, 2, 1), ${ParamB}) OR in(NULL, ${ParamB})),
{dataset_field},
NULL
)
This is essentially checking that Param A equals the first part of your string, and param B equals the second part of the string.
For a single select param:
ifelse(
contains({dataset_field}, ${ParamA}) AND contains({dataset_field}, ${ParamB}), {dataset_field},
NULL)
Multi-select parameters sometimes cause issues with some instances similar to this so let me know if that formula provides any errors
I actually found another way to implement this just recently which worked well for me. In my case, I made a single value drop down parameter that provides options for what combination of variables I would like to use to make separate lines for, and then use a switch statement that takes that choice and concatenates the fields. Something like this:
switch(${Parameter_Choose_Combination},
“Option1 X Option2”, concat({option1_datasetfield}, " - ",{option2_datasetfield}),
“Option2”, {option2_datasetfield}),
“Option1 X Option2 X Option3”, concat({option1_datasetfield}, " - ",{option2_datasetfield}, " - ", {option3_datasetfield}),
concat({option1_datasetfield}, " - ",{option3_datasetfield}))
Here, I can use the parameter to choose what combination of variables I would like to use, and then the switch statement reads the parameter value chosen and creates the separate lines for each combination.