topic Re: How to Aggr a RangeSum Command in QlikView

How to Aggr a RangeSum Command

BojiRigger — Sat, 16 Nov 2024 19:18:49 GMT

I am attempting to create an expression using RangeSum command and aggr.

I am trying to create a running % of sales by a line grouping.

The row is by Product with the Product Grouped in a higher level grouping call prodcat

In other words, I want to see the % of sales by product X the prodcat

When I enter the following, all works well.

=(Rangesum(Above(Sum(line_total),0,RowNo(TOTAL))))/Sum(TOTAL line_total)

When I attempt to aggr by prodcat, I get sporadic results, only one row in the grouping returns a %.

=(Rangesum(Above(Sum(line_total),0,RowNo(TOTAL))))/aggr(Sum(TOTAL line_total),prodcat)

I am new to Qlikview so be gentle??? LOL

marcus_sommer — Tue, 14 Jan 2020 14:40:19 GMT

Your approach missed an outer aggregation - in this way it's rather a calculated dimension. Therefore try it in this way:

avg(aggr(Sum(TOTAL line_total),prodcat))
or maybe with
aggr(NODISTINCT Sum(TOTAL line_total),prodcat)

- Marcus

BojiRigger — Thu, 16 Jan 2020 19:22:29 GMT

Marcus,

Thank you so much. Changing divisor to aggr(NODISTINCT Sum(TOTAL line_total),prodcat) worked perfectly.

Really appreciate the advice!

Thanks,

Randy