How to use a dimension containing several expressions in a graph ?

[#1]

Hi everyone,

I try to create two charts : a straight table + a radar that should display several KPI which I want to be able to select from a ListBox (if one is selected the charts should display it and hide it if not selected). Every KPI being calculated with a dedicated formula.

This is what I tried to do

• In the script, I have loaded my KPI expressions (KPI_Formula) and the KPI headers (KPI_Header) into a small table.
• I have created a straight table with KPI_Header as a dimension and  $(=KPI_Formula) as the expression.
• I have created a radar chart the same way

The problem I have is that the two charts show the result correctly only when 1 KPI is selected.

When I select more than one KPI in my ListBox it doesn't work as I intended and shows no results.

I have also tried differently in the straight table. I created one different expression for each KPI and set the Conditional show/hide according to the selection of the ListBox.

It works, but it is much less flexible that what I intended with the Headers and Formulas loaded in the script.

Also I can not do the Radar chart I want this way.

Does anyone know how I could make my charts display the results when more than one KPI is selected ???

Thanks!

[adamdavi3s · Accepted solution]

You might have to share your application, but I can sort of see why it is clashing as it doesn't know what exactly to calculate when you have more than one selected (because dollar sign expansion only fires once and not per line)

I just had a similar issue and managed to dynamically build a pick/match formula to tell the system what it should be doing for each row but this may or may not work in your scenario.

SO in my case this works, I have a variable called v_exp_report_pickmatch and it is defined as:

'pick(match(_ReportDetailedHeading,'&Concat(chr(39)&_ReportDetailedHeading,chr(39)&',',_ReportDetailedRow)&chr(39)&'),'&Concat(_ReportDetailedExpression,',',_ReportDetailedRow)&')'

where:

_ReportDetailedHeading -is the KPI name which also appears in the table

_ReportDetailedRow - is a simple rowno() on the table, it means everything gets ordered correctly

_ReportDetailedExpression - is the formula

I then just use the following in my chart:

$(=$(v_exp_report_pickmatch))

[#3]

Thank you Adam for your quick answer !

I will try this soon

[#4]

It works like a charm, great !

Now I just have to understand why   !

[adamdavi3s]

Hi Valentin,

I'll hopefully be doing a write up on this solution soon as I couldn't find anything like it on the forum (although there must be somewhere).

Basically all the pick(match does is say when the dimension value is this, use this formula, its surprisingly simple really.

The trick is because EVERY possible row is created in the pick(match formula, it doesn't matter that the dollar expansion only happens once for the chart because it only needs to happen once... if that makes sense!