Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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
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.