topic Re: Sum columns across pivot table in QlikView

Sum columns across pivot table

Thu, 28 Jun 2012 18:51:43 GMT

i'm looking for a little help using a pivot table in QV 11. i have 4 dimensions (type1, type2, name, and month). the month dimension is horizontal, the rest vertical. i currently have an expression to show "1" if the combination appears in the load tables

if(Count(distinct type1&type2&name)>0,1,0)

this works fine, but i would like to sum across all months and i'm finding it more difficult than i thought it would be. i want to keep the structure of the table as it is now and the months are automatically added as we enter a new one, so [May] + [April] + ..... is out of the question. the total column can be either a calculated dimension or an expression. i have tried aggr, sum rowno(total) and i thought just about everything else (although there is a good chance i was not doing them correctly). can somebody help me out? thanks.

Re: Sum columns across pivot table

Jason_Michaelides — Thu, 28 Jun 2012 20:03:58 GMT

Do you just want a total of all the 1's in the far right of the table? If so have you set the partial sums for Month?

Jason

Re: Sum columns across pivot table

Thu, 28 Jun 2012 20:09:39 GMT

Jason, i have tried showing partial sums on month and it just gives 1 for every type.

Re: Sum columns across pivot table

Jason_Michaelides — Thu, 28 Jun 2012 22:03:02 GMT

Can you post your app?

Re: Sum columns across pivot table

Fri, 29 Jun 2012 13:45:30 GMT

Thanks Jason. I actually got it figured out. using the below expression i am able to get my count by month (1 or 0) and the total column actually sums across. i thought i had tried every combination of sum aggr possible.

sum(aggr(Count(distinct type1&type2&name),type1, type2, name))

Re: Sum columns across pivot table

Fri, 29 Jun 2012 18:27:48 GMT

in a follow up, this gave me what i was looking for, but is there a way to reference the "partial sum" column in an expression or conditional format? so this expression now gives me 1 or 0 for each month horizontally, then a total at the end. so if a "type1&type2&name" combo appears in each month from Jan-May, the total is 5. i would like to set a conditional format to use that total. so if the total is 1, it means it is the first time this "type1&type2&name" is appearing, and i would like to highlight the cell (or change the expression to include if.... then "NEW")

Re: Sum columns across pivot table

Jason_Michaelides — Fri, 29 Jun 2012 19:02:57 GMT

If you post your app we can help more.

Re: Sum columns across pivot table

Mon, 02 Jul 2012 13:23:20 GMT

unfortunately i can't post the app. thanks again for your help, i'm sure i will get it figured out eventually.

Re: Sum columns across pivot table

Jason_Michaelides — Mon, 02 Jul 2012 15:51:40 GMT

OK, good luck. If you can find the time to either build a dummy example or scramble the existing data you will find it's much easier for people to help you.

Jason