Distinct Gross sum

[Anonymous]

office	Geo	manager	gross
11	1	2	299
22	2	2	299
33	2	1	608
33	2	3	333
22	1	4	608

Expecting O/P as Distinct values and Sum as follows in a Pivot Table Got Distinct values need Sum(premiums)

ABC	Distinct	Sum(gross)
office	3	11(299),22(907),33(941)
Geo	2	1(907),2(1240)
Manager	4	2(598),1(608),3(333),4(608)

[Anonymous]

can any one Help in this while writting set analysis not getting exactly.

[jagan · Accepted solution]

Hi,

Try like this

Dimension:

Add Calculated Dimension: =ValueList('Amount', 'Manager', 'Geo-code', 'Office')

Expression:

Distinct

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Amount', Count(DISTINCT Amount),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Geo-code', Count(DISTINCT [Geo-code]),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Manager', Count(DISTINCT Manager),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Office', Count(DISTINCT Office)))))

Distinct1

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Manager', Concat(TOTAL Aggr( Sum(gross) & '(' & MaxString(Manager) & ')', Manager), ', '),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Amount', Concat(TOTAL Aggr( Sum(gross) & '(' & Amount & ')', Amount), ', '),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Geo-code',Concat(TOTAL Aggr( Sum([gross]) & '(' & [Geo-code] & ')', [Geo-code]), ', '),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Office',  Concat(TOTAL Aggr( Sum(gross) & '(' & Office & ')', Office), ', ')))))

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Thanx jagan You Helped the day.First project on QlikView can i know Better Approach Dig more on Set Analysis & Scripting

[jagan]

Hi,

Refer Qlikview Reference Manual and Qlikview 11 for Developers books and follow the community postings, if possible try to answer.

Please close this thread.

Regards,

Jagan.