Is it possible to use AGGR inside a PICK Expression?

Mon, 03 Mar 2014 16:02:35 GMT

I'm updating an existing application that is using a detached table (columns are _KPI and Seq) and a pick expression to combine several expressions into one expression. It currently looks like below:

Dimensions:

_KPI

TimeDrilldown

Expression:

=pick(min({1} Seq),

num(sum([Metric1]), '#,##0'),

num(sum([Metric2]), '$#,##0;($#,##0)'),

num(sum([Metric3a]) / sum([Metric3b]), '#,##0.0%'),

...

)

This works fine. However, I have some metrics that are more complicated than a simple aggregate - some values are point in time.

The data is loaded weekly, so I need the last week in whatever TimeDrilldown value is selected. My solution was the following expression:

num(FirstSortedValue(DISTINCT AGGR(SUM(Metric4),WeekOrder),-WeekOrder), '#,##0')

While this expression works fine as a separate expression, It doesn't seem to work inside the pick, it just returns a blank value.

Any thoughts?

Thanks,

Will

Update:

I was talking with a coworker and he made a comment that turned out to be correct. _KPI, though irrelevant to the equation directly, was necessary to make the calculation work. So the solution is:

num(FirstSortedValue(DISTINCT AGGR(SUM(Metric4),_KPI,WeekOrder),-WeekOrder), '#,##0')

I tried adding the TimeDrilldown as well and it seemed to make no difference.

]

Message was edited by: Will Ford

topic Is it possible to use AGGR inside a PICK Expression? in QlikView

Is it possible to use AGGR inside a PICK Expression?