Calculate Data Series Dinamically

[Former Member]

Hello Everyone,

I've a requirement in Visual Composer that I would like to know if anyone can help me.

I want to build a lines chart.

In the chart you can define what you want to put in the "category Axis", that normally is defined dynamically.

In the data series you cannot define them dinamically.

For example you have this table/query:

Month/Process A B C D ...

01 33

02 34

03 35

...

So in a lines chart, you can define the "category axis" as Month, and in this case, if you have 3 months, the chart adapts to only 3 months, if you have 5 months, the chart adapts and displays the data for the 5 months.

But for the data series, I have to insert (in my example), all the process that exist, process A, B, C, D, etc., so for example if you have 100 process combinations, but only have data for three process, you have to define your dataseries for 100 process, and all 97 process that have the value zero (or no value) are displayed on the chart and represented in the legend.

My requirement is to display dynamically on the data series, only those that have in fact values, so in my example if only the process A, B and D had values, only those process should be displayed on the chart, if for other query execution only the process B and D had values, than, only the process B and D should be displayed.

Does anyone knows how to dinamically display the data series on a chart in Visual Composer?

Thanks for your help,

Diogo.

[Former Member · Accepted solution]

Hi

You can use filter for this. What you need to do is -

1. Drag filter from the output of the query & join filter output to the chart.

2. In the filter enter expression 'not 0*' for all your 'Data series'

3. Create chart as usual 'Month' on 'Category Axis' & Data series.

4. Save & Deploy the model.

So at run time when you execute this model, you will get only non zero value data series in the line chart.

Please let me know if you are looking for something else.

Regards

Sandeep

[Former Member · Accepted solution]

Hello Sandeep,

For that issue I got another workaround,

let's say that one of my dataseries is named xpto_value,

So in dataseries of the chart I define a formula something like this:

NVAL(IF(@XPTO_VALUE==0,"",@XPTO_value))

So if I have 0 for the XPTO_VALUE it is returned nothing, and the line of the data serie is not printed in the chart, otherwise it is printed.

I can work with this.

But my question is I have to do that for all the data series, i.e., for all the columns of the query.

So in my case, I have for now 6 columns, 6 data series, and I can define this for all these 6 data series in the chart.

But let's say that tomorrow, I have new data on my query and instead of 6 data series, I have 8. In this case the chart is not adapt dynamically, i.e., I still have only the 6 data series, I have to go once again to the chart and create this formula for the 2 new data series, having all the 8.

So the chart does not adapt dynamically for all the data series that exist at the time of execution, I have always to define the data series one by one.

Do you know a way of dynamically define the data series, so if the query returns n columns, then the chart will display automatically n data series independent of what n number is?

Thanks,

Diogo.

[Former Member · Accepted solution]

Hi

Are you using BI query as your data service? If Yes then you can create 'Structure' with key figures in the BEx query Rows.

& then apply your formula or filter as i mentioned. So when you modify the BEx query structure then it will reflect in VC dynamically.

Regards

Sandeep

[Former Member · Accepted solution]

Hi Sandeep,

With that solution I still have to manually adapt/change the structure each time a new value (new data serie) appears. I wanted that dinamically.

With the BEx we can have something like this:

A query with calendar month characteristic in the row and with a characteristic plant in the columns and displaying the amount for those combinations (month/plant)

So, if you created a chart in the BEx for that query, after executing it all the rows and columns (all the months and plants) would appear in the chart.

So lets say that tomorrow 3 new plants appear with amount for it to the corresponding months. If you execute the query tomorrow, this 3 new plants would appear automatically in the query, and the chart would automatically format 3 new data series without any manual change to it.

With the VC, if new plants appear, I have to define a structure with the addition of the three new plants and manually add 3 new data series to the chart to represent this 3 new plants.

I was trying to figure out a way of avoiding the manual changes to the chart each time new data series appear.

Any idea?

Thank's,

Diogo.