Exclude selection in if,aggr and set analysis expression

[tomovangel]

Hello, dear members,

I have a rather interesting case. I have to exclude selections in the following expression. 

sum(aggr(if(FirstSortedValue(distinct {<generated_r1={"<=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'written_off' and [Include Written Off Loans] ='No',

Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'1st_reminder_fee'}>}amount))
,ref_nr_fr1)
)

But, I don't Know if it is possible, since inside the expression I have a value that requires selection. 

I have tried with Total, with {1} and Only. 
Currently I don't know what more to try, I have looked in the forums, but nothing comes to mind. 

If you have any ideas i will gladly welcome them 😉 

I tried to make a sample app, but I couldn't prepare it with everything that has the expression.. 

[marcus_sommer · Accepted solution]

With the following logic ... {< Field1 =, Field2 = >} ... could you ignore the set selections in those fields. You might need to add all your relevant selections and also applying it to all your condition-parts, meaning to use for everyting an aggregation-function and also considering if it needs to be applied within the inner and/or the outer aggregation of the aggr-construct.

- Marcus

[tomovangel]

I tried your logic, but unfortunately i have 9 fields that have selections, and the becomes quite troublesome to write for 20+ columns. 

So i decided to proceed with this:

sum({1}aggr(if(FirstSortedValue(distinct {1<generated_r1={">=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'written_off',

Sum({1<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'1st_reminder_fee'}>}amount))
,ref_nr_fr1)
)


And the selection, that has to be applied in order for the expression to be evaluated ended up being in the properties of the columns like this : 

Show column if: 
Include written off loans = 'YES' 

So this was quite an easy approach, I haven't used this before.

very nice little setting

[marcus_sommer]

Enforcing the whole dataset with the {1} is a way but it could have disadvantages especially if you didn't could hide NULL and 0 within the dimensions and expressions. Therefore I prefer rather to specify all fields within the set analysis whereby it's often useful to use variables for it and/or to use expressions, for example:

... {< $(= concat({< $Table = {'Calendar'}>} $Field, '= ,')) >} ...

to exclude all fields from the calendar.

- Marcus