Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

How to sum distinct dimensions

Hi there,

I have a table like this:

Year
Month
Field 1
Field 2
Field 3
Vol
201211SGL100
201211SOL150
201211SGC100
201211SOC150
201211SPC200
20131SGL100
20131SOL75
20131SGC100
20131SOC75
20131SPC200
20131SSC75

And I want to get the sum of all the distincts Field 2, like this

Year

MonthField 2Vol
201211G100
201211O150
201211P200
Total

450
20131G100
20131O75
20131P200
20131S75
Total

450

to create a table which displays the sum (vol) by Field 3 and the Total Vol (sum all distincts Field 2) by year, exactly like this:

Field 1Field 3Year - MonthVolTotal Vol
SL2012 - 11250450
SC2012 - 11450450
SL2013 - 01175450
SC2013 - 01450450

I've tried

Total vol = sum(TOTAL aggr(sum(distinct Vol),Field 1,Field 2) )

but only works when I select a year and a month

Thanks,

Bere

2 Replies
Not applicable

Re: How to sum distinct dimensions

try this code

take a pivot table--

Dimension is Year, Field1, Field2, Field3, Month

in expression

sum(Aggr(sum(DISTINCT Vol),[Field 2],Year))

then in presentation tab select Field2 and then check on Partial sum

then output like this

YearField 1Field 2Field 3MonthVol
2012SGL11100
2012SOL11150
2012SPC11200
2012STotal

450
2013SGL1100
2013SOL175
2013SPC1200
2013SSC175
2013STotal

450

see ttachement

Re: How to sum distinct dimensions

Hi,

     Try this.

     Create a chart with dimensions as Field1,Field2,Year,Month

     And expression as

     Vol : - Sum(Vol)

     Total Vol : - Sum(Total <Year> Vol)

Regards,

Kaushik Solanki