Jump to content
We've recently updated our Privacy Statement, available here. ×

How to reference the value of 'Series by' in an expression on a Combination chart


Jo Goos

Recommended Posts

Hello,

For a custom tooltip on a Combination chart, I would like to use the value of then hovered over 'Series by' value in an expression.

The Tooltip screen for a Combination chart allows showing the 'Series by' value, but how can the same value be referenced in an expression

Thanks for a quick answer,

Jo

Link to comment
Share on other sites

Can you provide the custom expression you are trying to execute.

As you already have values for Series By column, your custom tooltip will be calculated based on Axis.X and Axis.Color by default. So say if you have various states on series by and you want to reference count of stores for Texas state in California then it would not be possible. Hence, it depends on how you are intending to you that value.

Link to comment
Share on other sites

I have a table with the following columns

ID Target Date Value
A 25% Achieved 02-JAN-2019 3
A 50% Achieved 03-JAN-2019 5
A 75% Achieved 05-JAN-2019 8
A 100% Achieved 08-JAN-2019 10
A Detail 01-JAN-2019 1
A Detail 01-JAN-2019 1
A Detail 02-JAN-2019 1
A Detail 03-JAN-2019 1
A Detail 03-JAN-2019 1
A Detail 05-JAN-2019 1
A Detail 05-JAN-2019 1
A Detail 05-JAN-2019 1
A Detail 08-JAN-2019 1
A Detail 08-JAN-2019 1
The data is displayed as follows:
  • Series
    • Bar: 25% Achieved

      Expression: First(If([Target]="25% Achieved",[Value],NULL)

    • Bar: 50% Achieved

      Expression: First(If([Target]="50% Achieved",[Value],NULL)

    • Bar: 70% Achieved

      Expression: First(If([Target]="75% Achieved",[Value],NULL)

    • Bar: 100% Achieved

      Expression: First(If([Target]="100% Achieved",[Value],NULL)

    • Line: Detail

      Expression: First(If([Target]="Detail",Sum([Detail]) OVER (AllPrevious([Axis.X])),NULL))

The data is displayed on a timescale for the [Date] column on Month, Quarter and Year level.

Idealy, the tooltip should be set as Max([Date]) OVER [Target] to get the date when hovering over the bars or line dot. However, the OVER statement is not working here.I then created 3 columns, doing the OVER calculation in a calculated column, and made the expression as follows, based on the hierarchy level dat was chosen for the [Date]:First(If("${Axis.X.DisplayName}" = "Date (Year)", ([Date over Year]), If("${Axis.X.DisplayName}" = "Date (Quarter)", ([Date over Quarter]), If("${Axis.X.DisplayName}" = "Date (Month)", ([Date over Month]), NULL)))) as [Tooltip Value]But this still gives the issue that when for instance the bar values fall within the same month, the value is taking the First of the 3 bars. So therefore I was searching for an expression to get the value from the 'Series By' value.

Link to comment
Share on other sites

This is because for each value expression in Y-axis, you are considering complete dataset there is no intersection with Target column and in series as well you have (Column Names) selected. So when calculating tooltip it will not divide your dataset by Target even if you add over clause. If somehow you are able to extract max value using over clause by Target, there is no way to assign it to specific bars as we cannot extract the specific bar displayname either through Y-AXis or through series by.

 

So in order to achieve your requirement, you can change your Y-Axis expression and have Target column in Series By.

 

You can Y-Axis expression like below which will sum up details value for previous months and keep the current value for rest of the target:

 

Sum(case  when [Target]="Detail" then [Value] else 0 end) OVER (AllPrevious([Axis.X])) 
+ Sum(case  when [Target]<>"Detail" then [Value] else 0 end) as [Value]

 

And now you can just add Max(Date) as a custom tooltip, which will always show you the max date for that target.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...