Search code examples
qlikviewqliksenseset-analysis

Get average of all the table's average - Qlikview

Is it possible to get the average value of each straight table and get the average again?

Example: Avg(1.99%+3.66%+3.49%+2.26%+2.14%+2.61%+5.54%+3.11%+2.92%+1.06%)

enter image description here

Solution

  • It depends on the complexity of your table expressions, but the general Idea is to "simulate" your straight/pivot table totals via an aggr functions:

    for example, for Table where the dimension is Week and the expressions is "sum(val)", you could do something like this in a variable:

    avg(aggr( sum(val), Week))

    if you have multiple dimensions in a Table, then just add them to the aggr fragmentation (ie: avg(aggr( sum(val), Week,Group)))

    then run all these calc/variables in rangeAvg In your case you could do something like:

        rangeAvg(
    aggr(Sum({$<[A]={$(=vVariable1)}, [Group]={'TableA'}>} [Duration])/Sum({$<[A]={'*'}, [Group]={'TableA'}>} [Duration]),Week),
    aggr(Sum({$<[A]={$(=vVariable1)}, [Group]={'TableA'}>} [Duration])/Sum({$<[A]={'*'}, [Group]={'TableA'}>} [Duration]),MonthYear),
    aggr(Sum({$<[A]={$(=vVariable1)}, [Group]={'TableA'}>} [Duration])/Sum({$<[A]={'*'}, [Group]={'TableA'}>} [Duration]),Year)
)

    side note: I wont get into vb macro as it is only relevant to qlikview and cannot be used in qliksense, but in general it is possible to access cell values via vb