Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
david_smee
New Contributor

Creating a Dimension from an aggregate value.

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

Capture.JPG

 

Thanks

 

 

 

 

2 Replies
rachel_delany
Contributor

Re: Creating a Dimension from an aggregate value.

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.

Highlighted
MynhardtBurger
New Contributor III

Re: Creating a Dimension from an aggregate value.

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