QlikView: Sum'ing Data Grouped by Aggregate Function

[Amos]

Hello.

I have an Aggregate function to split out Customers into bands based on a value

Aggr(

  IF(Sum([Value]) >= 1000000, '>1m',

    IF(Sum([Value]) < 1000000 and Sum([Value]) >= 500000, '<1m ->500k',

      IF(Sum([Value]) < 500000 and Sum([Value]) >= 50000, '<500k - >50k',

IF(Sum([Value]) < 50000, '<50k')

      )

    )

  ), [Customer]

)

What I would like to do is Sum the value within a certain band, which I felt would be the case with this:

So for the band <500k - >50k it would be:

Sum(Aggr(IF(Sum([Value]) < 500000 and Sum([Value]) >= 50000, [Value]), [Customer]))

The number it is returning is incorrect and I cannot work out the logic in the number to guess where the error is. The Customer is unique to the data but can appear on many rows in the table, so I am wondering if this is my issue...

Would anybody have any ideas where I am going wrong?

I have tried to find the answer as I do suspect I am missing something obvious and simple... so apologies for asking the question.

[marcus_sommer]

Your first aggr() is a calculated dimension and being applied in a chart you wouldn't need extra conditions within the expressions else just applying simple measures:

sum(Value)
count(distinct Customer)
sum(Value) / count(distinct Customer)
...

[Amos]

Hi, thanks for the reply.

I feel that I get what you are saying about it use within a chart but for how I am trying to display the data I do feel that I need to get the Sum'ed value for a specific band of data.

I am trying to create a Bar and Line graph combination where I band the Value as a Bar graph and then Line the Count of the Customer and they use that across multiple years.

Which would end up something like this:

The QlikView Line Graph does seem to want to recognise the banding which gives me something like this:

I appreciate that it won't be a "like for like" comparison, but my thinking is that if I can capture the value per band then I can fake the bands (rather than use the AGGR function) that are there as individual expressions and so build the graph.

Appreciate that this was not in the original question but also felt that most people did not want to read all of the above 🙂

So thanks for the suggestion but I don't feel it will work for how I want to display the data to the user.

I also get that I can have two graphs (line and bar) and overlay them and make things transparent so that it looks like one full graph, but the graphs seem to get out of line with each other quickly.

Thanks again.

[marcus_sommer]

To develop any logic and evaluating the data it would be better to use a table-chart because all graphical visuals have more or less limitations respectively capabilities - especially in regard to the available number of dimensions and expressions. If it worked like expected within a table-chart and not in a graphical visual it means that the visual with the applied configurations doesn't support the wanted view. Sometimes it may possible to adjust it appropriate or it makes clear that another kind of visualization would be needed.

Beside this you may need to extend the aggr() with more/other dimensions. Your current version considered only the Customer but not the Year.