Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
Is there a way I can create a conditional dimension to achieve following by using data attached in excel sheet? it should do following to get to the conditional dimension
1. Sum(OrderCount) Grouped by Client_GID
2. Sort step 1 by Asc
3. Get cummulative Sum using step 2
4. Get Cummulative % using step 3
5. if (Cumm% < 10, 1,
if(Cumm% <20, 2,
if(Cumm%<30, 3 etc...)
once I get conditional dimesion, I want to use it to count (Client_GID). In my example it will be
Dimension Count(client_GID)
1 552
2 278
3 156
etc....
Dimension has to recalcualte every time user makes different selection. Thanks for your help.
As per my understanding, The Dimension value will change Dynamically based on user selected Dimesion?
Look at this, use it in the Script Editor, add only the values you want in the Table "Quinta", and for me this one wors,
Best Regards!!!
Ventas:
LOAD
Dummie,
Client_GID
FROM
[..\Archivos Base\QVD\Extracciones.QVD]
(qvd) ;
Segunda:
load
Sum(OrderCount) as OrderSum
Resident Ventas
;
let vMaxOrder = num(Peek('OrderSum',0,'Segunda'));
Tercera:
load
Sum(OrderCount) as OrderGroup,
Client_GID
Resident Ventas
Group by Client_GID;
Cuarta:
load
OrderGroup / $(vMaxOrder) as PercentOrder,
Client_GID
resident Tercera
order by ClienT_GID desc;
Quinta:
load
if(RowNo()=1,PercentOrder,PercentOrder + Peek(AccumOrder,-1)) as AccumOrder,
Client_GID
Resident Cuarta
order by PercentOrder desc;
join (Ventas)
load
Client_GID,
if(AccumOrder < .1,'1',IF(AccumOrder < .2 ,'2','3')) as Dimension
Resident Quinta;
// DROP Table Segunda;
//DROP Table Tercera;
//DROP Table Cuarta;
// DROP Table Quinta;
Thanks for your suggestion. However, doing this in ETL will not make this dynamic. I want to make it dynamic so it changes based on user selections in front end. Thanks.