Replace conditional sum using aggr with Set expression.

[StevedeP_BI2Analytics]

Hi,

Is it possible to replace the below Qlik Expression with a version that uses set expressions? I have been searching and trying like crazy, but I am stuck..

Thanks!

     sum( //sum voor de aggr..
              Aggr(
                        If(
                            ([Material_Consumption.Case_Creation_Date])>=(RangeMax(Min(Date_M),StartDate)) 	and
                            ([Material_Consumption.Case_Creation_Date])<=(RangeMin(Max(Date_M),EndDate)) 	and 
                            [Material_Consumption.Category]='Contract Covered' and 
                            ([BS] = 'Consumption - Good part' OR [BS] = 'Consumption - Reversal')
                        ,  sum([Material_Consumption.Amount_ListPrice_EUR]) 
                        , Null)
                    ,ContractId, StartDate, EndDate, ConcatKey_MC, [Material_Consumption.Case_Creation_Date], Material
                    )
                )
            )

[marcus_sommer]

The Category and BS could be written as set expressions within the aggregations. But by the various dates it depends on the data-model and the requirements of this calculation. The set analysis worked like a selection on a column-level - this means in your regard the queried and compared date-values would be needed the same for all rows within your calculation. Otherwise if a row-level evaluation is needed because each contract, material and so on has different date-values you will need an if-loop like you have.

Beside of this it might be helpful to transfer parts or the entire logic into the data-model. Maybe by resolving the start- and end-dates to a single date with an intervalmatch and/or comparing the various dates within the script by creating flags and/or offset-values ...