
Re: Create a calculated dimension by measure
Stefan Wühl May 28, 2016 10:22 AM (in response to Rohit Kumar)I guess your internal grouping entity should be Customer?
In the script:
INPUT:
LOAD * INLINE [
Customer, Order_No, Amount
A, Order1, 100
A, Order2, 400
B , Order3 , 500
C , Order4 , 400
C, Order5, 200
];
LOAD Customer, AvgOrderAmount,
If(AvgOrderAmount <200, Dual('<200',200),
If(AvgOrderAmount <500, Dual('>=200 <500',500),Dual('>=500',520))) as Group;
LOAD Customer, Sum(Amount) / Count(Order_No) as AvgOrderAmount
RESIDENT INPUT
Group By Customer;
You can also use
= If(Sum(Amount) / Count(Order_No) <200, Dual('<200',200),
If(Sum(Amount) / Count(Order_No) <500, Dual('>=200 <500',500),Dual('>=500',520)))
as calculated dimension without the additional data model change

Re: Create a calculated dimension by measure
Rohit Kumar May 28, 2016 1:33 PM (in response to Stefan Wühl )Hi ,
Thanks for the reply , I think I couldn't tell you the exact problem, there is a field Gross Amount , and NO_() which is the order number and i need to create a dimension which has 5 values , <1500, 1500< and 3500> , 3500< and less than 7500, 7500< and <15000 and 15000<. I have tried your above formula but it is giving me an invalid dimension , Can you please help in this , this is on urgent basis requirement
for two chunks I have tried below one
If(Sum(Gross_Amount) / Count(No_) <1500, Dual('<1500',1500),
If(Sum(Gross_Amount) / Count(No_) <3500, Dual('>=1500 <3500',3500),Dual('>=3500',3520)))

Re: Create a calculated dimension by measure
Sunny Talwar May 28, 2016 7:20 PM (in response to Rohit Kumar)Try this using the aggr function:
Aggr(If(Sum(Gross_Amount) / Count(No_) <1500, Dual('<1500',1500),
If(Sum(Gross_Amount) / Count(No_) <3500, Dual('>=1500 <3500',3500),Dual('>=3500',3520))), NO_)

Re: Create a calculated dimension by measure
Stefan Wühl May 28, 2016 7:43 PM (in response to Rohit Kumar)It would be easier to answer if you could post some sample records and your expected result.
If your data looks like
Gross Amount, NO_()
100, 1
1600,2
3000,3
1000,4
i.e. you don't need to aggregate per NO_(), then you don't need the aggr() function:
create a calculated dimension like
=If( [Gross Amount] < 1500, Dual('<1500',1500),
If( [Gross Amount] < 3500, Dual('>=1500 <3500',3500),
If( [Gross Amount] < 7500, Dual('>=3500 <7500',7500),
If( [Gross Amount] < 15000, Dual('>=7500 <15000',15000),Dual('>=15000 ',20000)))))
As expression, use
=Count(DISTINCT [NO_()])
If you need to aggregate the amounts per NO_(), add the aggr() around the if()
=Aggr(
If( Sum([Gross Amount])/Count([NO_()] < 1500, Dual('<1500',1500),
If( Sum([Gross Amount])/Count([NO_()] < 3500, Dual('>=1500 <3500',3500),
If(Sum([Gross Amount])/Count([NO_()] < 7500, Dual('>=3500 <7500',7500),
If( Sum([Gross Amount])/Count([NO_()]< 15000, Dual('>=7500 <15000',15000),Dual('>=15000 ',20000)))))
,[NO_()] )
edit: added the aggregations to the second sample.


Re: Create a calculated dimension by measure
Sunny Talwar May 28, 2016 5:14 PM (in response to Stefan Wühl )Stefan correct me if you think I am wrong, but won't we need Aggr() function here for the calculated dimension?
=Aggr(If(Sum(Amount)/Count(Order_No) < 200, Dual('<200', 200),
If(Sum(Amount)/Count(Order_No) < 500, Dual('>=200 <500', 500), Dual('>=500', 520))), Customer)

Re: Create a calculated dimension by measure
Stefan Wühl May 28, 2016 5:46 PM (in response to Sunny Talwar )You're right of course, Sunny, we need the advanced aggregation when we want to calculate the avg order amount per Customer and then create the buckets based on these values (just like in the load script).



Re: Create a calculated dimension by measure
Rohit Kumar May 28, 2016 11:57 PM (in response to Rohit Kumar)Hi swuehl,
You above answer is the best solution for my problem. Its should be appreciated and saved my lots of time . thanks a lot.