Complicated set analysis + nested + P()

[alexandernatale]

Hello everybody,

I need to do a nested set analysis.

In essence, I have to add the value field for the year 2019 and by account, however, considering only the department codes that have a progressive count of the products >0...

I tried to write this formula, but the result is not correct...I feel that - maybe - I'm close to the solution but I can't find it

=sum({<Year={"2019"}, COD_CONTO={"0701*"}, codice_cdrs=P({<codice_cdrs={"$(=count(num_registro)>0)"}>}codice_cdrs)>}VALORE)

[marcus_sommer]

There is no condition within the count() which means it would be calculated against the current selection state. Therefore you may try it with this:

=sum({<Year={"2019"}, COD_CONTO={"0701*"}, codice_cdrs=P({<codice_cdrs={"$(=count({<Year={"2019"}, COD_CONTO={"0701*"}>} num_registro)>0)"}>}codice_cdrs)>}VALORE)

Depending on the data-structures you might not mandatory need the count-part else checking for the exists of products may also be working, like:

=sum({<Year={"2019"}, COD_CONTO={"0701*"}, num_registro = {"*"}>}VALORE)

[alexandernatale]

hello @marcus_sommer  thanks for reply.

This formula:

=sum({<Year={"2019"}, COD_CONTO={"0701*"}, codice_cdrs=P({<codice_cdrs={"$(=count({<Year={"2019"}, COD_CONTO={"0701*"}>} num_registro)>0)"}>}codice_cdrs)>}VALORE)

don't work.

the constraint on the count must be made only on the num_register (which represents the number of orders). So my intent is to add the "VALORE" field of only codice_cdrs (departments) that have had a count(num_registro)>0

[marcus_sommer]

Without a deeper knowledge of the data-model and the data it's difficult to make a suggestion whereby I would try at first to simplify the logic by splitting the expression within more logically parts, for example with the following approach:

if(count({<Year={"2019"}, COD_CONTO={"0701*"}>} num_registro)>0),
    sum({<Year={"2019"}, COD_CONTO={"0701*"}>} VALORE))

The reason is that a $-sign expansion like $(= ...) creates an adhoc-variable which is calculated ones before the chart is evaluated and the result will be then applied for all rows. Without $(= ...) just by applying an approach like:

... Field = {"=count(Field)>0"} ...

it's not a real set analysis (column-level evaluation) anymore else an if-loop (row-level evaluation) within the set analysis syntax with no benefits against a "classical" if-loop.

Further it's not quite clear for me if your calculation is depending on a certain dimensionality which isn't provided within the chart. In this case you would need to wrap the calculation with an aggr(), like:

sum(aggr(Expression, Dim1, Dim2)

whereby also the outer aggregation might need a set analysis condition and/or any TOTAL statement.