Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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