Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Skip the ticket, Chat with Qlik Support instead for instant assistance.
Showing results for 
Search instead for 
Did you mean: 

Replace conditional sum using aggr with Set expression.


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



     sum( //sum voor de aggr..
                            ([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
MVP & Luminary
MVP & Luminary

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