topic Re: Dimension Bucket in App Development

Dimension Bucket

Nirpanthan — Thu, 06 May 2021 08:34:25 GMT

Add a computed "ACTIVITY LEVEL" dimension for customers in:

ACTIVITY LEVEL possible values:

High if avg pairs / day >= 3
Medium if avg pairs / day >=1 and < 3
Low if avg pairs / day >= 0.5 and < 1
V Low if avg pairs / day > 0 and < 0.5
Inactive if avg pairs / day = 0

To compute "avg pairs / day",

for Compute: nb of orders / nb of days in month
over the last completed month

Re: Dimension Bucket

ali_hijazi — Thu, 06 May 2021 10:10:28 GMT

you need first to aggregate your calculation to a dimension or set of dimensions
aggr(

if(

nb_of_Orders / nb_of_days >= 3, 'High'

,if(nb_of_Orders / nb_of_days >=1 and nb_of_Orders / nb_of_days < 3 , 'Medium'

,if(nb_of_Orders / nb_of_days >=0.5 and nb_of_Orders / nb_of_days <1, 'Low'

,...

)))

,month)

alternatively you can use the class function but the class function gives you results over fixe-size intervals

Re: Dimension Bucket

Nirpanthan — Thu, 06 May 2021 10:12:37 GMT

i want to do it in the dimension level ... front end i am able to achieve ..

Re: Dimension Bucket

ali_hijazi — Thu, 06 May 2021 10:15:01 GMT

you mean in the script?
then you need to calculate the number of orders group by customer, month
then you do the average calculation per row (customer, month)
then put the if condition to classify the result as either high, medium, low,...

Re: Dimension Bucket

Nirpanthan — Thu, 06 May 2021 10:23:51 GMT

Yes in the script level

Re: Dimension Bucket

Nirpanthan — Thu, 06 May 2021 10:31:33 GMT

Can you give me syntax will be so useful

i am totally strucked

Re: Dimension Bucket

ali_hijazi — Thu, 06 May 2021 11:27:41 GMT

the script would be something like this:

t1:
Load Customer, Date, count(Order_id) as [Number of Orders], Floor(MonthEnd(Date)) - Floor(MonthStart(Date)) + 1 as [Number of days in Month]
resident your_table
group by Customer, Month(Date);

then:
t2:
load * , if(avg>= 3, 'High', if(avg>=1 and avg <3, 'Medium',if(avg>=0.5 and avg <1, 'Low'))) as Classification

load *, [Number of Orders] / [Number of days in Month] as avg
resident t1;
drop table t1;

hope this helps

Re: Dimension Bucket

ali_hijazi — Thu, 06 May 2021 11:36:38 GMT

but I personally prefer doing things on the UI and keep the script as much simple as possible

Re: Dimension Bucket

Nirpanthan — Thu, 06 May 2021 11:38:43 GMT

Explained .. Still they want from the backend even i have done Master dimension...

I am loading the data once done will update .

Thanks in advance....