Replace conditional sum using aggr with Set expression.

StevedeP_BI2Analytics — Fri, 24 Mar 2023 11:38:52 GMT

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

Re: Replace conditional sum using aggr with Set expression.

marcus_sommer — Fri, 24 Mar 2023 17:23:34 GMT

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

topic Replace conditional sum using aggr with Set expression. in QlikView

Replace conditional sum using aggr with Set expression.

Re: Replace conditional sum using aggr with Set expression.