Multiple Custom Buckets from the Same Field Using Calculated Dimensions

[TemoLogua]

Hello Qlik Community,

I would like to share an interesting solution for handling several custom buckets placed on the same chart. These buckets are created as calculated dimensions based on the same field, and this same field is also used on the chart itself as a calculated dimension.

First, I recommend reviewing the topic shared by @rwunderlich 

This article explains an important concept regarding calculated dimensions. When you filter a calculated dimension, regardless of its internal logic, the field used inside the Aggr() function is actually the field being filtered.

Problem

The problem is the following: we have at least three filtering options that are all based on a single physical field.

For example, suppose we want to display the User field in a table chart as a calculated dimension (Logic A), where we only show users that satisfy a specific condition.

{1}
Aggr(
Only(
{1
<UserID *= {"=Sum({1<FlagDeposit = {'1'}, Date = {"">=$(=AddMonths('$(vlCurrentLoadTime)',-3))""}>} Deposit) >= $(vdMinDeposit)"}
>
} UserID
)
,
UserID
)

At the same time, we want to display:

User bucket 'Status' based on the total amount of deposits (Logic B)
= {1}
Aggr(
Pick(
Match( Num(true()),
Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) < $(vdBucStatusMin1),

Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) >= $(vdBucStatusMin1) and
Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) < $(vdBucStatusMin2),

Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) >= $(vdBucStatusMin2) and
Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) < $(vdBucStatusMin3),

Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) >= $(vdBucStatusMin3) and
Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) < $(vdBucStatusMin4),

Sum({1<FlagDeposit = {'1'}, Date = {">=$(=AddMonths('$(vlCurrentLoadTime)',-3))"}>} Deposit) >= $(vdBucStatusMin4)
),
Dual('N/A',5), Dual('LOW',4), Dual('MIDDLE',3), Dual('SUPER',2), Dual('VIP',1)
),
UserID
)

and Bucket 'LDD' based on the number of days since the last deposit (Logic C)
= {1}
Aggr(
Pick(
Match( Num(true()),
Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) < 1,

Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) >= 1 and
Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) < 3,

Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) >= 3 and
Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) < 7,

Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) >= 7 and
Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) < 14,

Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) >= 14 and
Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) < 30,

Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) >= 30 and
Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) < 60,

Floor('$(vlCurrentLoadTime)') - Max({1<FlagDeposit = {'1'}>} Date) >= 60
),
Dual('0LDD',1), Dual('3LDD',2), Dual('7LDD',3), Dual('14LDD',4), Dual('30LDD',5), Dual('60LDD',6), Dual('>60LDD',7)
),
UserID
)

Solution

I solved this problem in the following way.

First, I created a filter pane using a calculated dimension based on Logic A, created an Alternate State called Status, and assigned it to this filter pane.

Then I created another filter pane using a calculated dimension based on Logic B, created an Alternate State called LDD, and assigned it to this filter pane.

Finally, in the table I displayed the User calculated dimension as follows:

{1}
Aggr(
Only(
{1
<UserID *= {"=Sum({1<FlagDeposit = {'1'}, Date = {"">=$(=AddMonths('$(vlCurrentLoadTime)',-3))""}>} Deposit) >= $(vdMinDeposit)"}
*
P({LDD * Status})
>
} UserID
)
,
UserID
)

Lastly, I displayed these buckets as calculated measures in the table chart using logic similar to the calculated dimensions.