Using a field as filter in a chart

[#1]

Dear community,

I've the following data structure:

CODE	LABEL	VALUE	COMPOSE
C1	O1	1	O1
C2	O2	2	O2,O1,O3
C3	O3	3	O3
C4	O4	4	O6,O1,O7
C5	O5	5	O5
C6	O6	6	O6
C7	O7	7	O7

I want a simple table chart where the value is summed according the COMPOSE filed, but I'm getting how.

The attached example shows my attempts to solve this issue using set analysis, sum(if(..)) and so on, that didn't work at all.

I mean they all work if a value of COMPOSE is selected, but that is not the objective, which was to have a a listing of CODE and respective TOTALs of COMPOSE.

Any help would be very welcomed.

Best regards,

pmb

[#2 · Accepted solution]

Good day,

First of all, it would be great if you provide the source, so that we can make a reload of your application

I've created a XLS file with the values you gave.

Your data are stored in a "TblData".

Next, I create a new table that says which CODE is composed of which LABEL (based on the "COMPOSE" field).

TblComposition:
LOAD CODE,
 SUBFIELD(COMPOSE, ',') AS COMPOSED_OF
RESIDENT TblData;

I make a JOIN with this table, to have the "distinct" value (the value of each unit) based on the TblData :

JOIN (TblComposition) LOAD
 LABEL AS COMPOSED_OF,
 VALUE AS UNIT_VALUE
RESIDENT TblData;

Now, I can know each composition of each code, and it's own value.

I can make a graph that looks like what you asked :

Is this the result you expected ?

[#3]

End here is the data file I used.

[montubhardwaj]

Hi,

Based on what I understood from your requirement,I have created a sample application. Please see the attached. See the chart in it which is showing sum of values based on Compose values.

Cheers,

Sharma

[#5]

Thank you Martin. Yes that is the result I was expecting and as such it is the solution of the problem although I was trying to explore a possibility to use a database field to act as a filter, eventually having some formatting to make the filter field according to the syntax required.

Let's say that fieldReference and fieldReferenceValue are a pair of simple accounting Items on a balance sheet.

The resume balance account Items are described by a list of simple Items that needs to be summed: fieldResume = "fieldReference1, fieldReference3"

I was trying use this field as a "filter" on a Set Analysis sum:

ResumeValue = sum ({<fieldReference = { filedResume }>}fieldReferenceValue)

This solution only works if I select a particular fieldResume, and not for the "entire table", do you know why ?

Any how your proposal is of course a very good solution for the problem.

Thank you & best regards,

Pmb

[#6]

Thank you Sharma, that is a good solution for the problem (please check my post to mmartin)

Best regards,

pmb

[#7]

pmbinnovfb wrote:
This solution only works if I select a particular fieldResume, and not for the "entire table", do you know why ?

I think that Set Analysis make a filter on data before the calculation of each line. The Set Analysis is common for the whole chart.

If you want something calculated for each line, you have to use a "if" statement.