topic min, aggr function in QlikView

min, aggr function

Mon, 14 Oct 2013 21:15:19 GMT

Hi All,

I am trying to achieve max month/selected month Avg score as text on trellis line chart for Min 3 providers. The expression I wrote is working fine month &Region selections but when I Sub-Region its not.

Am using Aggr, Min(Expr, Rank) functions.under presentation-->test in chart.

Ex: =NUM(Min(Aggr(Avg({$< MonthYear={"$(=Max(MonthYear))"},DQC_DATA_PROVIDER={"*"}>}OVERALL_PCT)/100, DQC_DATA_PROVIDER), 2), '####.##%')

I have attached a sample

Thanks in Advnace

Re: min, aggr function

stevedark — Mon, 14 Oct 2013 23:21:42 GMT

Hi Samuel,

This is indeed odd. What is strange is that you are using min values 2, 3 and 4 to get your Reference Points. I would say it is only when you select CA that these return the right values. In all other cases min 1 and 2 return the same values. The only thing I can assume is that rogue rows are being created by the AGGR function - but I can't see why that would be.

Is there any way you can simplify your data model and get it working like that before bringing in more associations?

Regards,
Steve

Re: min, aggr function

jduenyas — Tue, 15 Oct 2013 00:02:27 GMT

Is your MonthYear a text field or date (numeric)?

You may run into some problems if you are comparing numeric with text.

Also

">=$(=Date(addmonths(Max(MonthYear), -5), 'MMM YY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM YY'))"}

may cause problems because you are searching for what precedes what in string values and "Dec 12" is less than "Jan 12" in string comparison