Creating a Dimension from an aggregate value.

[david_smee]

Hi,

I am trying to create a dimension from this.  I get an error when I use it as a dimension but it displays if I use the following as a measure.  

IF(MAX(CaseAgingDaysByMonth) <=14, Dual('0-14', 1),
If(MAX(CaseAgingDaysByMonth) > 14 and MAX(CaseAgingDaysByMonth) <= 21, Dual('15-21', 2),
If(MAX(CaseAgingDaysByMonth) > 21 and MAX(CaseAgingDaysByMonth) <= 28, Dual('22-28', 3),
If(MAX(CaseAgingDaysByMonth) > 28, Dual('29+', 4)))))

What I need to do is get the max aging by date and assign that to "buckets" - Categorize them.

The reason for max is because I want to determine how old something is based on the month end that a use selects.  

Sample -  Case Aging Buckets is what I want as a dimension.  It only works as a measure for some reason.  I need this to be a dimension so I can use in a bar chart.  If the user selects MonthEndDate of 2014-11-30 then the aging buckets should be 15-21

Thanks

[rachel_delany]

Hi,

I think you just need to use the AGGR function with it, and aggregate at whatever level you're looking for.

For example, aggregated at the CaseNbr level:

AGGR(
IF(MAX(CaseAgingDaysByMonth) <=14, Dual('0-14', 1),
If(MAX(CaseAgingDaysByMonth) > 14 and MAX(CaseAgingDaysByMonth) <= 21, Dual('15-21', 2),
If(MAX(CaseAgingDaysByMonth) > 21 and MAX(CaseAgingDaysByMonth) <= 28, Dual('22-28', 3),
If(MAX(CaseAgingDaysByMonth) > 28, Dual('29+', 4)))))
,CaseNbr)

Also, consider whether you could calculate this in the load script instead.

[MynhardtBurger]

I agree with Rachel_delany.

You need to wrap your measure in AGGR.  Additionally I suggest making use of the CLASS function to create your buckets/bins. 

Try the following:

AGGR(
    CLASS(MAX(CaseAgingDaysByMonth), 7),
    CaseNbr, MonthEndDate
)

This might also help: https://community.qlik.com/t5/Qlik-Design-Blog/Buckets/ba-p/1463216