
Re: Calculate a new dimension based on a measure
Sunny Talwar Mar 1, 2017 4:26 AM (in response to Giulio Andrea Toscano)You averaged everything after reaching more than 2000 for product A? Since the condition is <4000, doesn't it needs to stop D7? instead of D8?

Re: Calculate a new dimension based on a measure
Giulio Andrea Toscano Mar 1, 2017 4:32 AM (in response to Sunny Talwar )you are right sorry Sunny, so in this case, for product A, the margin for D8 should go in another cluster <6000. The same also for product C I am going to modify the file.
Thank you again.

Re: Calculate a new dimension based on a measure
Sunny Talwar Mar 1, 2017 4:34 AM (in response to Giulio Andrea Toscano)How many clusters are there or is this something that will be dynamic? Also, these clusters, do you imagine them created in the script or on the front end?

Re: Calculate a new dimension based on a measure
Giulio Andrea Toscano Mar 1, 2017 4:41 AM (in response to Sunny Talwar )The cluster will be static like 2000  4000  6000 10000  ecc. But I could consider also a dinamic solution. Ideally the best way for me will be in the front end, but if it is impossible i could consider also a previous aggregation in the script.




Re: Calculate a new dimension based on a measure
Anagha Rao Mar 1, 2017 4:33 AM (in response to Giulio Andrea Toscano)Hi Giulio,
Is this what you were after?

q.qvw 149.5 K


Re: Calculate a new dimension based on a measure
omar bensalem Mar 1, 2017 5:12 AM (in response to Giulio Andrea Toscano)try this:
Finale:
LOAD
product,
"Sales price"
FROM [lib://source/PROVA_3.xlsx]
(ooxml, embedded labels, table is Data);
Left Join(Finale)
LOAD
product,
sum("Sales price") as SommeSales
Resident Finale
Group By product;
SalesTable:
LOAD product,
"Sales price",
if(SommeSales<=4000,'<=4000','>4000') as "Sales Amount"
Resident Finale;
DROP Table Finale;
here is the result I got:
If you wanna include Margin, here is what I did:
In the script:
Finale:
LOAD
product,
"Sales price",
"Margin %" AS Margin
FROM [lib://source/PROVA_3.xlsx]
(ooxml, embedded labels, table is Data);
Left Join(Finale)
LOAD
product,
sum("Sales price") as SommeSales,
Avg(Margin) as AvgMargin
Resident Finale
Group By product;
SalesTable:
LOAD product,
"Sales price",
AvgMargin,
if(SommeSales<=4000,'<=4000','>4000') as "Sales Amount"
Resident Finale;
DROP Table Finale;
Hope this could help you !
Ben Salem Omar,

Re: Calculate a new dimension based on a measure
Giulio Andrea Toscano Mar 1, 2017 5:32 AM (in response to omar bensalem )Thanks Omar, but I need another thing. I need to create a new dimension based on a measure, I need to sum the sales until they reach 2000 and then until they reach 4000 and calculate the average margin. I need to see every field completed in my table.
Anyway thank you for your time


Re: Calculate a new dimension based on a measure
Sunny Talwar Mar 1, 2017 6:22 AM (in response to Giulio Andrea Toscano)Something along these lines?
Dimension
product
Aggr(
If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 2000, Dual('< 2000', 1),
If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 4000, Dual('< 4000', 2),
If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 6000, Dual('< 6000', 3),
If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 8000, Dual('< 8000', 4),
If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 10000, Dual('< 10000', 5)))))), product, (date,(NUMERICA, DESC)))
Expression
Avg([Margin %])

251697.qvf 176.0 K

Re: Calculate a new dimension based on a measure
omar bensalem Mar 1, 2017 7:32 AM (in response to Sunny Talwar )And the MVP iiiiis : as always Thank you Sunny !
Now if I wanted to create this in the script; as I've done, what should we do?
Re: Calculate a new dimension based on a measure
Sunny Talwar Mar 1, 2017 7:44 AM (in response to omar bensalem )You would need a script like this
Table:
LOAD
product,
"date",
"Sales price",
"Margin %"
FROM [lib://Lib/PROVA_3.xlsx]
(ooxml, embedded labels, table is Data);
FinalTable:
LOAD *,
If(CumSales < 2000, Dual('< 2000', 1),
If(CumSales < 4000, Dual('< 4000', 2),
If(CumSales < 6000, Dual('< 6000', 3),
If(CumSales < 8000, Dual('< 8000', 4),
If(CumSales < 10000, Dual('< 10000', 5)))))) as Buckets;
LOAD *,
If(product = Previous(product), RangeSum(Peek('CumSales'), "Sales price"), "Sales price") as CumSales
Resident Table
Order By product, date desc;
DROP Table Table;

251697.qvf 176.0 K



Re: Calculate a new dimension based on a measure
Giulio Andrea Toscano Mar 3, 2017 4:13 AM (in response to Sunny Talwar )Thank you Sunny this is the correct answer!!!
