Different range for two metrics in singl visual

I am creating single time series line chart visual for two different aggregated value:
The end user can select the start week and end week to filter the visual for their analysis.
1.average weekly cheque of product with the range for this time-series is from the [Start week] to the [End week]
2.average wekly cheque of category with the range for this time-series should run from [Start week] - 4 Fiscal Weeks (“FW”) to the [End Week].
For an example:
So if the user give start week = 6 and end week = 10. The range for the

  1. metric 1 would be week 6 to 10
  2. metric 2 would be (6-4) i.e 2 to 10

How can I set the start range for these two metric into single visual? So that on X-Axis the week number start from 2 to 10 with Line chart for metric 1 start from 6 to 10 and metric 2 the line chart start from 2 to 10

Hello @dna !

I recommend using the extract function for the date field that you want to use for your x axis field well:

Then if you have the Start Week and End Week as parameters I would recommend creating an ifelse state that includes the logic for the two lines and apply that to the value field well.

1 Like

Hello @dna !

I gave this another look and I think there is a better way than my initial response.

You would probably want to use addDateTime with an ifelse statement to accomplish this:

Metric Lines: (Value Field Well)

{datefield} >= ${StartWeek} AND 
{datefield} <= ${EndWeek}, 
{datefield}  >= addDateTime(-2, 'WK', ${StarWeek) AND 
{datefield}  <= ${EndDate},

X Axis field well:
dateDiff(${StartWeek}, ${EndWeek},'WK') + 1

For the x axis you may need to experiment to get the weeks to count according to your data set. For now I left a place holder value for a date time value.

@duncan Thank you for the response. But I Did not get it.
As of now I have created a single visual for both the metrics. I created two calculated field as below:

sumIf({weekly_amount},{product_item_name}=${productitemname}) / distinct_countIf({registered_account_id},{product_item_name}=${productitemname})

sumIf({weekly_amount},{category_type}=${category}) / distinct_countIf({registered_account_id},{category_type}=${category})

Then I created a line chart visual between X: week_date and Value field: weekly_product_avg, weekly_category_avg.
I have created a two control parameter StartWeek and EndWeek for user to filter the visual.
Would you please help me what changes I need to make so that weekly_product_avg will have range between (StartWeek) To (EndWeek) and weekly_category_avg: will have range (StartWeek-4 week) To (EndWeek) on X axis.

Hey @dna!

Did the calculated field I mentioned throw an error or did the values not come back as expected?

For your calculations to work you will need to use a set of parameters to tell the calculated fields where they need to measure. It will depend on your data, for example if you have an order_date or product_date fields that you can use as ${StartWeek} and ${EndWeek}. You can also do something like creating specific parameters for each calc but depending on how your end user will interact with the dashboard it could become tedious. Then in your two calculated field above and the logic similar to my ifelse statement.

{order_date} >= ${StartWeek} AND {order_date} <= ${EndWeek}

{order_date} >= addDateTime(-2, 'WK', ${StarWeek}) AND {order_date} <= ${EndWeek}

1 Like