Onthe fly data eliminations and dynamic set analysis

Sun, 19 Apr 2015 09:26:53 GMT

Dear All,

I am working on data consolidation and intercompany flows elimination on the fly.

My case a company (RU = reporting unit) A sales to a company B (ICP = interco partner that is a group RU)

B may sale to A

C sales to B

etc....

As we are a group we want to eliminate flows between them based on RU hierarchies

example :

when L1 = ASIA, I want to elimate flows between A & B

when R_hiearchy = On demand, I want to eliminate flows between A B & C, but not D

More or less, what we are saying ELIM = sales for ICP = set of current RU lineked to the displayed level

I am able to generate DynFormula that could be the right formula but not able to apply it on the fly : the systems takes ICP = All RUin On demande instead of staying in ASIA level

Example

Re: Onthe fly data eliminations and dynamic set analysis

Sun, 19 Apr 2015 09:37:35 GMT

I share my Qlikview sample

DynamicElim = =-sum( {$<ICP={ $(=CONCAT(Chr(39)&RU&Chr(39),','))

}>} Data)

toElimSet = = Concat( DISTINCT Chr(39) &RU &Chr(39), ',' )

DynFormula = ='-sum( {$<ICP={' & Concat( DISTINCT Chr(39) &RU &Chr(39), ',' ) & '}>} Data)

right columns =-sum( {$<ICP={ $(=Concat( DISTINCT Chr(39) &RU &Chr(39), ',' )) }>} Data)

topic Re: Onthe fly data eliminations and dynamic set analysis in QlikView

Onthe fly data eliminations and dynamic set analysis

Re: Onthe fly data eliminations and dynamic set analysis