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

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Buzze
Contributor II
Contributor II

Stop RangeSum from aggregating for all displayed dimension values

Hello community,

I am building a chart that displays:

  • Dimension: work day of month calculated in script
If(WeekDay(OrderCreationDate) > 5
		,null()
	    ,NetWorkDays(MonthStart(OrderCreationDate),OrderCreationDate)
	    )										as OrderCreationWorkdayInMonth,
  • Expression 1 = [Order Intake Current Month]: cumulative order intake volume for current month until day of reload
RangeSum(
Above(
    Sum(
        {<
            Year=
            ,Month=
            ,Date=
            ,OrderCreationDate={">=$(=Date(MonthStart(Today(1))))<=$(=Date(Today(1)))"}
            ,RequestedDeliveryDateFromOrderLine={">=$(=Date(MonthStart(Today(1))))<=$(=Date(Floor(MonthEnd(Today(1)))))"}
            >}
            _OrderIntakeQty
            )
          ,0
        ,$(=NetWorkDays(MonthStart(Today(1)-1),Today(1)))
          )
      )
      /1000000
  • Expression 2: average order intake volume for full current month in previous two years (will be extended to 3 year average once we have 3 years of data)
([Order Intake CM Year-1]+[Order Intake CM Year-2])/2
  • Expression 3: relative % between the two expressions above.
[Order Intake Current Month]/[Avg Order Intake CM last 2 years]

QlikCommunity-1.png

The formula for the first expression is rangesum where I have limited the calculation until workday of today-1 through set analysis, but because the 2-year average has to be displayed for the full month, the aggregation of the first expression continues for all displayed dimension values. Basically I want the part that I have marked with the red square not to be displayed.

If I turn the first expression into an IF function where I say "IF(RangeSum() < 2, null(), RangeSum())" they are indeed not being displayed when looking at the totals, but of course this limit of 2 isn't dynamic at all, so when selections are being made it doesn't work anymore. I tried to come up with a way to change this 2 by a dynamically calculated value relative to the RangeSum() but I haven't found a good calculation yet. 

So I see two options:

  1. I hope there is a way to stop the accumulation from happening after the point that I have defined in Set Analysis. Any suggestions?
  2. Calculate a relative limiter value that I could use to replace the 2 in this IF-statement: "IF(RangeSum() < 2, null(), RangeSum())". Any ideas?

Thanks!

Labels (3)
0 Replies