topic Re: create conditional dimension using cummulative % in QlikView

create conditional dimension using cummulative %

nimeshpa — Thu, 07 Nov 2013 21:43:28 GMT

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.

Re: create conditional dimension using cummulative %

Thu, 07 Nov 2013 23:26:32 GMT

As per my understanding, The Dimension value will change Dynamically based on user selected Dimesion?

Re: create conditional dimension using cummulative %

Thu, 07 Nov 2013 23:27:44 GMT

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;

Re: create conditional dimension using cummulative %

nimeshpa — Tue, 19 Nov 2013 15:41:59 GMT

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.