topic Re: Distinct Gross sum in QlikView

Distinct Gross sum

Anonymous — Wed, 27 Aug 2014 10:21:31 GMT

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

Anonymous — Wed, 27 Aug 2014 11:06:28 GMT

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

jagan — Wed, 27 Aug 2014 12:04:39 GMT

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 — Wed, 27 Aug 2014 14:05:26 GMT

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

jagan — Wed, 27 Aug 2014 14:12:45 GMT

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.