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