Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
i have this set expression:
aggr(RangeSum(Above(total sum({<YearMonth ={"<=$(=Max(YearMonth))"}, Year = {$(=Max(Year))} >} cod_connid_2_3_4_6_7_v), 0, YearMonth)),Country,YearMonth)
which aggregates a YTD expression and give to me this result (YTD CY Total Entered Calls)
Now, im trying to achieve the same results but splitted in YearWeek division, using this set expression
aggr(RangeSum(Above(total sum({<YearMonth ={"<=$(=Max(YearMonth))"}, Year = {$(=Max(Year))} >} cod_connid_2_3_4_6_7_v), 0, RowNo())),Country,Yearweek)
Anyway,even the numbers are correct, are splitted anomally
The correct result should be this
As you can see, sorting will not help because the number are associated to wrong dimension.
i can't figure out where the problem is, any idea?
Hi!
I managed to solve in this way
aggr(RangeSum(Above(total sum({<YearMonth ={"<=$(=Max(YearMonth))"}, Year = {$(=Max(Year))} >} cod_connid_2_3_4_6_7_v), 0, RowNo())),Country,(Yearweek,TEXT,ASCENDING))
and it's working !
The Aggr() function generates an array of values that have its own sort order - different from the sort order of the visible table. To sort the Aggr() array, you need a structured parameter. See https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/ag...
But why do you use the Aggr() at all? If you just use the RangeSum() measure, this will calculated from the visible table - which is what you want. So, try this instead:
RangeSum(Above(total sum({<YearMonth ={"<=$(=Max(YearMonth))"}, Year = {$(=Max(Year))} >} cod_connid_2_3_4_6_7_v), 0, RowNo(total)))
hi hic,
your solution doesn't work,
if i use you formula it always give me previous month
so that's why i use the aggr().
Oh yes, a solution with RangeSum() does indeed work. See image below, where you clearly can see that it accumulates correctly.
If you don't get the result you want, I think you should try to find the error instead of using Aggr(). Aggr() is slower and could cause other problems.
Hi!
I managed to solve in this way
aggr(RangeSum(Above(total sum({<YearMonth ={"<=$(=Max(YearMonth))"}, Year = {$(=Max(Year))} >} cod_connid_2_3_4_6_7_v), 0, RowNo())),Country,(Yearweek,TEXT,ASCENDING))
and it's working !