Monday, 11 April 2011

TOTAL and AGGR in chart expression

I've created a customer / product profitiability Qlikview report.  Most of the charts are built around fairly simple queries, such as list by Customer / Product and Month and show Sum(Sales), Sum(Margin) etc.

It also includes a more complex Analysis chart (straight table).  The chart dimensions include lists Customers, Products, Dates and Order numbers - so at the transaction level.  Some of the expressions are looking at First Price and Last Price and comparing each transaction's price to that.

So one of the Expressions has to calculate the First Price for a Customer / Product and show it against all the related transactions.  Because the date range is dynamic, can't calculate this in the script.

Instead have to do it through an expression, which include uses TOTAL qualifier and AGGR expressions.  After much trial and error got a working version, but took me even longer to understand out why it works.

In this example the fields I'm using are:
- CustProd.  Concatentation of the Customer and Product (actually use an Autonumber to convert this concatenation to integer so that is more efficient to calculate)
- TransDate.  Will ignore Order Number, and assume only one CustProd per date for convenience.
- Price.  Precalculated in script at transaction level. 

Expression is:
=Sum(TOTAL <CustProd>
           Aggr(
                    If(TransDate = Min(TOTAL <CustProd> TransDate),Price),
                    CustProd,TransDate)
          )

How does this work?

Some notes on what TOTAL and AGGR do first - as I understand them.
TOTAL means the expression (Sum, Min) etc. is applied across all the records available to the chart, rather than just those matching the dimensions on this row.  If you include a field in the <...>, then it will create a subtotal for each unique combination of those fields.

AGGR on the other hand creates a mini-chart based on the expr and dimension.  The mini-chart will have one expression (the one defined within the Aggr function) and one or more dimensions based on the fields listed.

Example data may be
CustProd    TransDate     Price
123             1/1/11          100
123             2/1/11          110
123             3/1/11          110

Working from the inside,
Min(TOTAL <CustProd> TransDate).  Fairly easy.  This generates a list of CustProd records and the lowest TransDate for each.  If this was an expression it would return the first transdate for each Customer / Product.  Useful, but we want the price, not the date.

Next step, is to get the Price for the first date.
If(TransDate = Min(TOTAL <CustProd> TransDate),Price)
This will return the Price is the TransDate is the first date for this CustProd record.  Problem is that in the chart, we list all dates, so this only shows the Price on the first record.  We want to see the first price on all records.

To do this, need to apply the first Sum(TOTAL <CustProd> so that it all records for a CustProd can access the first price.  But can't do this without putting in an AGGR function b/w the Sum and the Min.

The AGGR(If(TransDate = Min(TOTAL <CustProd> TransDate),Price),CustProd,TransDate) creates a mini-chart that looks like this:
CustProd    TransDate     Price
123             1/1/11          100

Only the first date is returned as all other dates fail the If(TransDate = Min...) test and so return Null (which is ignored).
What's important is that every record for this CustProd will create the same chart - if it has access to all records of that CustProd - which it does as the first Sum function has TOTAL <CustProd> in it.

The final Sum(TOTAL <CustProd> .... ) simply sums the Price from that MiniChart and works out the first price is always 100.

4 comments:

  1. Thanks so much for taking time for this writeup. Very helpful.

    One (perhaps obvious) question... the final Sum(...), how does it know to sum the PRICE? Your Aggr() returns the mini-chart or rowset, so to speak, which include the dimensions CustProd, TransDate... so does the Sum() in this case just assume the expression of the Aggr(), which is Price?

    Normally, a Sum() would need an expression to sum, for example:
    Sum(TOTAL expression)

    ReplyDelete
  2. Hi Delta,

    Yes your right. The mini-chart / rowset can only have one expression - the aggregation function (Sum, Count, Max) that's within the Aggr(). In this case it's Price.

    So the outer Sum works on that returned expression.

    Regards,
    Michael

    Sorry for delay - don't have much time to work on this.

    ReplyDelete
  3. hey delta see this
    • Participation to the whole:
    Sum([# Departures Performed]) / Sum(TOTAL [# Departures
    Performed])
    This expression will return the percentage of flights for a particular carrier,
    month, and year relative to the total flights of all carriers, and for all years
    and months available in the current selection state.

    • Percentage versus the carrier's whole:
    Sum([# Departures Performed]) / Sum(TOTAL <[Carrier Group],
    Airline> [# Departures Performed])
    This one will return the percentage of flights for a particular carrier, month,
    and year relative to the total flights performed by that carrier in all years and
    months available in the current selection state.
    • Percentage versus the carrier's whole per year:
    Sum([# Departures Performed]) / Sum(TOTAL <[Carrier Group],
    Airline, Year> [# Departures Performed])
    This will return the percentage of flights for a particular carrier, month, and
    year relative to the total flights performed by that carrier in that year, but
    for all months available in the current selection state that correspond to that
    same year.

    i think now u clarified about TOTAL quailfier

    Regards
    Premhas

    ReplyDelete
  4. Hey Micheal thanks excellent article liked it
    continue publishing more ..

    ReplyDelete