Dynamic Grouping Based On Calculated Conditions

[#1]

Hi Experts,

May I request for your assistance?

I created a pivot table in Qlik which resulted to the table below. However, I need to apply certain rules/computations which will help assign an agent to only 1 group and sub group. I have attached an XLS file which specifies the requirements and additional information needed  (such as date selection, rules, etc.).

Group	Sub- Group	Agents	Count	Sales	Target
A	1	MULTI101,SINGLE101	2	1460	3000
	2		1	450	6000
		SUB-TOTAL	3	1910	9000
B	3	MULTI102, SINGLE102	2	820	6000
	4	MULTI102, SINGLE103	2	1130	4000
		SUB-TOTAL	4	1950	10000

Below is the expected result after rules are applied.

Group	Sub- Group	Agents	Count	Sales	Target
A	1	SINGLE101	1	1460	3000
	2	MULTI101,	1	450	6000
		SUB-TOTAL	2	1910	9000
B	3	MULTI102, SINGLE102	2	820	6000
	4	SINGLE103	1	1130	4000
		SUB-TOTAL	3	1950	10000

If there are any clarifications regarding this item, please feel free to inform me.

Thank you in advance for your assistance.

[settu_periasamy]

Hi,

You can try something like in the script. But i'm not sure, how to link with you Date Filed. May be this script gives an idea to do in the Front End also.

T1:
LOAD * INLINE [
    COST CENTER, DATE, GROUP, SUBGROUP, AGENT, SALE, TARGET
    MAIN, 1-Jan-15, A, 1, MULTI101, 1300, 1000
    MAIN, 7-Jan-15, A, 2, MULTI101, 450, 6000
    MAIN, 12-Jan-15, A, 1, SINGLE101, 160, 2000
    MAIN, 15-Jan-15, B, 3, MULTI102, 470, 4000
    MAIN, 23-Jan-15, B, 4, MULTI102, 980, 3000
    MAIN, 24-Jan-15, B, 3, SINGLE102, 350, 2000
    MAIN, 31-Jan-15, B, 4, SINGLE103, 150, 1000
];

NoConcatenate
T2:
LOAD [COST CENTER], GROUP,
  SUBGROUP,
  CONCAT(AGENT,',') as AGENT,
  SUM(SALE) as SALE,
  SUM(TARGET) as TARGET
  Resident T1 Group by [COST CENTER], GROUP, SUBGROUP Order by SUBGROUP desc;

DROP Table T1;

NoConcatenate
T3:
LOAD [COST CENTER],GROUP,SUBGROUP,
  if(SubStringCount(AGENT,',')=0,AGENT,
  IF(SubField(AGENT,',',1)=Previous(SubField(AGENT,',',1)) and Previous(SubStringCount(AGENT,','))=0,
  SubField(AGENT,',',2),
  if( SubField(AGENT,',',1)=Previous(SubField(AGENT,',',1)) and TARGET>Previous(TARGET),
  AGENT,SubField(AGENT,',',2)))) as AGENT,
  SALE,
  TARGET
  Resident T2 Order by SUBGROUP desc;


DROP Table T2;

Or, You can even try with calculated dimension like

=Aggr(Concat(DISTINCT AGENT,','),GROUP,SUBGROUP)  // Tried this, but not get your expected result.

[#3]

Hi settu_periasamy,

The script is really useful. Thank you very much. However, it is a requirement that we consider the date selection. I am also trying to find a way to utilize what you have provided.

Still hoping to find an alternative to solve this matter considering the date selection.

Thank you for the new learning,

Wilfred