topic Re: Creating a Dimension from an aggregate value. in App Development

Creating a Dimension from an aggregate value.

david_smee — Fri, 02 Apr 2021 07:52:46 GMT

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

Re: Creating a Dimension from an aggregate value.

rachel_delany — Mon, 01 Apr 2019 05:17:55 GMT

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.

Re: Creating a Dimension from an aggregate value.

MynhardtBurger — Wed, 10 Apr 2019 05:23:29 GMT

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