Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
StevedeP_BI2Analytics
Contributor
Contributor

Replace conditional sum using aggr with Set expression.

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
                    )
                )
            )

 



Labels (3)
1 Reply
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 ...