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

[Jo Goos]

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

[Khushboo Rabadia]

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.

[Jo Goos]

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.

[Khushboo Rabadia]

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.