Dimension Reference line where Cumulative Sum is 50% of 'Final' total?

[110]

Here's an illustration from excel to best demonstrate 

I can do the Y-Axis reference lines to show where 50% if the total ( for 2024 ) is, but I'd like to also show the intersection on the X-Axis where the monthly number is approximate to 50% of the final number.

In Excel I am using a column that has cumulative total as % of final, then find the max value per month that's less or equal to 50% .

Is this possible to do in Qlik?

[110]

I've attached Qlik and Excel versions in this post. If that helps 🙂

I split the years to separate measures as I also couldn't get cumulative sum to work with aggregation / 2nd dimension on line chart.

In this chart I'd be looking to show ( using dimension reference line ) the point at which the 2024 'Sales' number was ~ 50%

[BPiotrowski]

Hi,
So I assume you need smth like this in your X reference line

=AVG(Aggr(Sum({<OrderYear={2024}>}Quantity),OrderMonth))

So you can get this

Hope it helps 😉

[110]

Exactly, but on the Cumulative sum, so for example :

'a' = RangeSum(Above(Total (　sum({<Year={2024}> }SalesTest)　) , 0, RowNo(Total)))

'b' = sum( Total {<Year={2024}>} SalesTest)　

Then I'd have some form of expression to return the lowest month where b<=a

'c'  = max(  if ( a<=b,Month )

Where it'd just return a single value returned 

[110 · Accepted solution]

I came up with this abomination of aggregation - but it appears to work..

Example for the 'Month' dimension ( shows first month where Sales hit above 50% of total )

=
min(
if(
aggr(
Aggr(RangeSum(Above(Total (　sum({<Year={2024}>}SalesTest)　) , 0, RowNo(Total))), Month)
,Year,Month)>aggr(sum( TOTAL {<Year={2024}>}SalesTest),Year,Month)*0.5,Month)
)

and for the label, to show the actual percentage for that dimension reference line . .

='Over 50% ( ' &

num(
aggr(
min(total
aggr(


if(
aggr(Aggr(RangeSum(Above(Total (　sum({<Year={2024}>}SalesTest)　) , 0, RowNo(Total))), Month),Year,Month)>aggr(sum( TOTAL {<Year={2024}>}SalesTest),Year,Month)*0.5,
min(Aggr(RangeSum(Above(Total (　sum({<Year={2024}>}SalesTest)　) , 0, RowNo(Total))), Month)
/aggr(sum( TOTAL {<Year={2024}>}SalesTest),Year,Month)
)

),Year,Month)
),
Year)


,'0.0%')

& ') '

There very well may be a simpler way to do this without so many aggregations.   Something that'd be very welcome would be some generic functions to do this with less syntax 😄