Table: get the correct result when the project to be excluded is in the current selections set of data

[ali_hijazi]

Hello I have the following table:

I have selected the above project names;

the expression is sum(amount)
now total amount is 35.79 which is correct
but in the expression I want to eliminate the projects whose parent_project_pfolio_1_id = 1 and pfolio_level_id = 18
so the last row shouldn't be calculated and thus the total should be 34.73 since the last row shouldn't be counted
so I wrote the below expression:

sum(

{$-<parent_project_pfolio_id_id = 1, pfolio_level_id=18>}

amount)
and I got the following result:

the total is correct however the results came under null scenario type and null year
but if I exclude the SUPPORT-BSRM_BSM project I get the fine result:

so how can I get the correct result when the project to be excluded is in the current selections set of data?
why when that project is available the other projects to be included come under null year and null scenario?

knowing that all the fields except the year and scenario are in the project table (dimension) linked to fact table via prjh_key and the year is part of the calendar table linked to fact table via the month_key and the scenario is part of the scenario table linked to fact table via scenario_key

kindly advise

[Nagaraju_KCS]

Try this

sum(
{$<ProjectName = P(ProjectName)> - <parent_project_pfolio_id_id = 1, pfolio_level_id=18>}amount)

or

sum({<parent_project_pfolio_id_id = E({$::parent_project_pfolio_id_id} - {'1'}),
pfolio_level_id = E({$::pfolio_level_id} - {'18'})>}amount)

[ali_hijazi]

the syntax of your expression is not correct
I corrected your expression above
sum(
{$<ProjectName = P(ProjectName)> - <parent_project_pfolio_id_id = 1, pfolio_level_id=18>}amount)
but also same behavior

[Nagaraju_KCS]

try this

sum({<
parent_project_pfolio_id_id -= {1},
pfolio_level_id -= {18}
>}amount)

[ali_hijazi]

this will eliminate the other projects as well because they got parent portfolio level 1 id = 1

[marcus_sommer]

If you want to exclude certain rows from a calculation by keeping these rows within the object and hidden ZEROS/NULLS you need to enforce the display within any other dimension/expression.

Alternatives may be to query the dimensionality() to branch into different calculations and/or adding some dummy-values, like:

rangesum(MyExpression, 0.00000000001)

The results are of course not quite correct but within the most views it won't be noticeable.

[ali_hijazi]

Hello @marcus_sommer 

This is not the issue tat we are talking about

I was saying that my expression excludes well what is intended to be excluded and the number is correct however, the result comes under null dimension values but if I only select the dimension values that I want from the filter pane the result is grouped under correct values of the dimensions used in the chart

Whereas if I select projects that are supposed to be excluded from the filter pane things go wrong knowing that on the chart there will be more expressions under which all projects might appear 

I know it's hard to explain the issue with texting here but Qlik doesn't seem to handle complex scenarios 

All I have to do is open the script and create composite fields something which is ridiculous 

[marcus_sommer]

I wouldn't say that Qlik couldn't handle complex scenarios because several set statements could be combined with any operator even with nested logic, like: ... F1 = p({< F2 = e(F2) >} F1) ... In general could be each available part of the data-set be addressed.

Be aware that this are just selections - not mandatory the needed relations to the object-dimensionalities and not to special view-requirements to show/hide anything respectively to include/exclude certain values within calculations by displaying the opposite.

In some scenarios might such approach be simplified by splitting the calculation into n base-calculations which return a wider result and which are limited from the outside with a boolean filter, like:

sum({ Set } aggr({ Set } sum({ Set } Field), Dim1, Dim2)) * -(MyCondition)

There are no real technically limitations but very often it isn't sensible to develop such complex stuff else it would be better to transfer the essential logic into the data-model.